| 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: query for top 10 sql?
Originally I thought the same but then noticed it was "query for" & "top ten sql" - which I interpreted as a query about SQL (similar to the Statspack type results).
As far as the SQL Server "TOP" equivalent - I have not seen such a graceful solution in Oracle. One approach I have seen includes using an ORDER BY in a subquery and applying "where rownum <= 10" in the outer query - if the inner query returns a large result set then a big sort is required. A second approach might be to use an analytical function like RANK() or ROW_NUM() - but again a large sort is often required. A third approach relies on using index ordering (with a hint typically for force index traversal) and "where rownum <= 10" applied on the original query - this depends on a suitable index being available and is quite risky (for example, if the index is dropped the query won't fail but will return a very incorrect result without warning).
"Nelson Flores"
<nflores_at_expand.cl To: <oracle-l_at_freelists.org>
> cc:
Sent by: Subject: RE: query for top 10 sql?
oracle-l-bounce_at_fr
eelists.org
11/03/2004 12:37
Please respond to
oracle-l
I think he means an SQL query where you get the first 10 rows...
Like the "Top" Command in SQL Server's T-SQL, or using DUAL in Oracle ..
>From what I know, there is no ANSI standard for this.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mark Richard
Sent: Wednesday, March 10, 2004 5:28 PM
To: oracle-l_at_freelists.org
Subject: Re: query for top 10 sql?
Ryan,
"Top ten" what? I think that's why there are different queries out
there.
It all depends on what you care about and I doubt that any specific
query
is right or wrong - just a different view or definition of "top ten".
"Ryan"
<ryan.gaffuri_at_cox. To:
<oracle-l_at_freelists.org>
net> cc:
Sent by: Subject: query for top
10 sql?
oracle-l-bounce_at_fr
eelists.org
11/03/2004 11:04
Please respond to
oracle-l
is there a standard query for this? Seems like people have different standards.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
----------------------------------------------------------------
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such a case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (03) 9612-6999 or (61) 3 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban Infrastructure Developments Limited and CityLink Melbourne Limited shall be understood as neither given nor endorsed by them. <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Wed Mar 10 2004 - 20:04:53 CST
 |
 |