Last Updated on
I came across this exam question several times in the past about how to get some top n values without using the LIMIT clause or its equivalent in other RDBMS (e.g., FIRST n in Informix) but never really gave it serious thought until today. I would skip the item and press on.
To achieve this, use a subquery. The following SQL SELECT statement retrieves the top 10 highest values from table T1.
SELECT A FROM t1
WHERE 10 > (SELECT COUNT(*) FROM t1 T1A
WHERE T1A.A > T1.A) ORDER BY A DESC
Retrieved top 10 highest values