• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

sql query question

 
Ranch Hand
Posts: 31
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
author & internet detective
Posts: 42165
937
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 31
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 236
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
author & internet detective
Posts: 42165
937
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 214
IntelliJ IDE Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
author
Posts: 3252
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 161
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic