The Top 'n' SQL Query is a common interview question and I've asked it a couple of times to fresh graduates who couldn't answer it - some didn't even know about the existence of the MAX group function.
I'm pretty sure everyone is familiar with the Oracle-specific statement:
SELECT sal FROM (
SELECT sal FROM mytable ORDER BY sal DESC
) WHERE ROWNUM < 10;
MySQL (and PostgreSQL) is pretty similar where you use a:
SELECT sal FROM mytable LIMIT 10;
With Microsoft SQL Server, you can use the SET ROWCOUNT 10 (Sybase supports this too) before executing a query to return ordered rows and this approach is similar to the MySQL approach. You can also use the TOP keyword (Firebird uses the FIRST keyword instead of the TOP keyword) as follows:
SELECT TOP 10 sal FROM mytable;
The TOP keyword also support the PERCENT keyword after the number, making it more flexible.
Also, with Microsoft SQL Server, you can use a ROW_NUMBER() OVER (ORDER BY column_name) to provide something similar to the ROWNUM pseudocolumn in Oracle. RANK is used similar to ROW_NUMBER, except that RANK supports the use of PARTITION BY (optional) in addition to ORDER BY.
I think an ANSI compliant statement that works across all databases would be something like this:
SELECT sal FROM mytable a WHERE 10 > (
SELECT COUNT(1) FROM mytable b
where b.sal > a.sal
)
ORDER BY sal DESC;
I think a more challenging question for a beginner would be to return the next highest after the Top 'n'. I think I'll save that for the time I'm interviewing experienced job applicants.
