sql query question

[Lester Tam]

Hello, im a MS-SQL user,

i need to rewrite a sql to allow it run in oracle 9i:

select top 100 * from pOrder where ...... order by createDate

i rewrite it as:

select * from pOrder where .... and rownum <=100 order by createDate

i find that it doestnt work because the database will first from a result table with rownum than sort it by date, than filter out all rownum >100 records, so the final result is not what i expecting.

Any Idea?
Lester
[ July 23, 2004: Message edited by: Lester Tam ]

[Jeanne Boyarsky]

Lester,
Just to clarify I'm understanding right: you want the top 100 records that meet your criteria and then just sort those by date?

I'm going to move this to JDBC as it has to do with SQL queries.

[Lester Tam]

Hm.. i try to make it more clear

in oracle,if i wrote:

select * from pOrder where .... and rownum <=100 order by createDate

it will join all the condition inside "..." , assign row number to it (base on the nature sorting), filter out all rownum >100 records, and finally sort it by date.


but what i need is:
join all the condition(except rownum<=100), sort it by date, assign rownum base on the date order, then filter out rownum > 100 and return


i think that this query will give me what i want:

select * from ( select * from pOrder where ... order by date ) where rownum<=100

but im worrying the performance as im simplified the query example here, the real one had joined several tables and each table have more than 1M rows..




Any Idea?
Lester

[Paul Santa Maria]

The last time I looked, Oracle did *not* have an equivalent of MS-SQL's "top N". That appears to still be the case:

Query for 10 10 SQL?

I'd check Metalink and some of the Oracle web pages.

Please let us know what you find!

[Jeanne Boyarsky]

Lester,
You are correct that you will need a nested query. The performance shouldn't be too bad, but you can use "explain" to check how Oracle would run the query.

[Edwin Keeton]

Oracle also supports the SAMPLE syntax similar to TOP in T-SQL. In either case, the difference is in syntax. I haven't tested it but I would guess that TOP versus a nested query would produce the same execution plan. I'd like to hear it if that is not the case.

[Peter den Haan]

SAMPLE is something totally different - it takes a random sample. Nesting your query inside a SELECT * FROM ( ... ) WHERE ROWNUM <= 100 is the easiest way to get a top 100 in Oracle.

- Peter

[Joe Nguyen]

The performance impact would be depend on the level of complexity of the inner query. If the inner query matches all specified conditions, retrieves 3000 records, sorts these records on created date, and returns just 100 records, performance would be bad. In this case, you may consider creating new index to reduce the performance impact