| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: SQL for top 100 values
This was a new feature in Oracle8i. In 7.3 the best way would be to open a cursor that sorted the data and return the first N rows.
Sorry,
John
ddayal_at_dol.state.nj.us wrote:
I tried to run it in Oracle 7.3.4, it comes up with a syntax errorHere is the SQL select x.fein,x.open_bal from (select sf_get_fein(employer_id) fein, nvl(total_open_balance_amt,0) open_bal from employer order by open_bal desc ) x where rownum < 101Here is what I getSQLWKS> select x.fein,x.open_bal 2> from (select sf_get_fein(employer_id) fein, nvl(total_open_balance_amt,0) open_bal 3> from employer 4> order by open_bal desc ) x 5> where rownum < 101 6> order by open_bal desc ) x *ORA-00907: missing right parenthesisIs it because I am on a lower versionThanksDeen-----Original Message-----FaroultSent: Thursday, January 10, 2002 11:26 AMTo: Multiple recipients of list ORACLE-LDeen Dayal wrote:
hi SQL wizards,Can any body help me with the tjis SQL. I need a SQL records with top 100 values of a table ( not rownum < 101 ).ThanksDeen
rownum < 101 is OK if you nest your query, ORDER BY included, as anin-line view,ie select x.val from (select val from my_table order by val desc) x where rownum < 101--Regards,Stephane FaroultOriole Ltd--Please see the official ORACLE-L FAQ: http://www.orafaq.com--Author: Stephane Faroult INET: sfaroult@oriole.comFat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing Lists--------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail messageto: ListGuru@fatcity.com (note
EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Received on Thu Jan 10 2002 - 12:57:36 CST
 |
 |