Re: nested SQL query

If by nesting you mean subqueries, no I don't think there is a way to do it. You can do a join to a subquery, but that would be considered nesting.

I was trying something like this --

SELECT * FROM EMP
WHERE rownum = 1
ORDER BY empno

but it didn't work every time because of the way Oracle parses. Actually I was amazed that it worked for some columns consistantly and not others. Anyone know why by chance? I was thinking indexes, but put an index on job and that didn't magically work. With the scott/tiger sample data, empno, sal and comm actually works doing this in 9i (comm seems to - there are nulls which come first, so I can't really tell)

Of course to use nesting, you could do this --

SELECT * FROM
    (SELECT * FROM EMP
     ORDER BY empno)
WHERE rownum = 1

If you are completely against nesting, you could run a batch job at night that creates a temporary table that holds the sheetidx and max(revision). It's a strange method, but I don't know what you are doing with the info and may be applicable to your situation.

Tom
www.oraclepower.com

"Erik" <erik_lembke_at_web.de> wrote in message news:eeb1dc43.0201280612.6c32ba23_at_posting.google.com...
> I don't know if this is the correct newsgroup, but I was not able to
> find a SQL group.
>
> Just a short question:
> Is it OK to use such SQL Statements:
>
> select REVISION, SHEETIDX from sheet where REVISION=(select
> MAX(REVISION) from SHEET);
>
> Problem is I want the sheetidx with the highest revision.
>
> Is there a solution with better performance (without a nest) ?
>
> regards,
>
> Erik
>
Received on Mon Jan 28 2002 - 10:06:46 CST