aspose file tools*
The moose likes JDBC and the fly likes sql query question Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "sql query question" Watch "sql query question" New topic
Author

sql query question

Lester Tam
Ranch Hand

Joined: Jun 19, 2003
Posts: 31
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30324
    
150

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Lester Tam
Ranch Hand

Joined: Jun 19, 2003
Posts: 31
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
Ranch Hand

Joined: Feb 24, 2004
Posts: 236
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!


Paul M. Santa Maria, SCJP
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30324
    
150

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
Ranch Hand

Joined: Jul 10, 2002
Posts: 214

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.


SCJP, SCWCD
Peter den Haan
author
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
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
Ranch Hand

Joined: Apr 20, 2001
Posts: 161
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
 
 
subject: sql query question