SQL: how can i get the 10 greatest values

[erwan]

In sql, i want to get only 10 gretest values on a table .

I try with ROWNUM with " where rownum > count(*) -10 but the "order by" is made after the rownum and the result is false.

how can i do that.
thank you!

 

[Guest_imported]

Hi,

U have to use co-related subquery in this context.

Maybe the following sample will help you :

To select top ten salaries from the emp table :

SELECT A.ENAME,A.SAL FROM EMP A
WHERE 10 > (SELECT COUNT(*) FROM EMP B
WHERE A.SAL > B.SAL);

 

[nw2394]

The last reply is OK, but should read a.sal<b.sal to do what you need. Also it will possibly be inefficient on a large table. If you have any kind of procedural control you can try the simple, but obvious approach of

select ename, sal
from emp
order by sal desc;

Then fetch the first n rows you're interested in.

Cheers

Nick Warren

 

[Shadow4269]

I've seen this question before. It was posted recently in SQL forum on 8/22/00 by Mackpei. I don't know if it will answer your question directly, but might give you a good starting point.

 

[carp]

SELECT *
FROM (SELECT *
FROM your_table
ORDER BY your_criteria DESC)
WHERE ROWNUM <= 10;

This will work in Oracle8.