MySQL, Software Development

Top n values without using a limit clause or its equivalent

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

1

2

3

SELECT A FROM t1

WHERE 10 > (SELECT COUNT(*) FROM t1 T1A

WHERE T1A.A > T1.A) ORDER BY A DESC

Sample data

Retrieved top 10 highest values