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!

 

[stoleru]

Let's say that column my_value is your criteria of ordering rows. Then:
SELECT * FROM your_table a
WHERE 10>(SELECT COUNT(*) FROM your_table b
WHERE b.my_value>a.my_value); will retrieve the firsts 10 rows (i.e. the rows which have no more than 9 superiors).
This should work for unique values of my_value column, for repeating values there are many scenarios.

 

[culshaja]

Use the TOP clause.

SELECT TOP 10 FROM tableA WHERE x = y ORDER BY z

The 10 highest (or lowest) values of Z will then be shown.

James

 

[culshaja]

Sorry!!!!
Realised that this is a SQLServer function (he, he)

James