Can there be a database-agnostic SQL query to fetch top N rows?

Question

We want to be able to select top N rows using a SQL Query. The target database could be Oracle or MySQL. Is there an elegant approach to this? (Needless to say, we're dealing with sorted data here.)

Answer 1

To get the top 5 scorers from this table:

CREATE TABLE people
             (id      int, 
              name    string, 
              score   int)

try this SQL:

SELECT id, 
       name, 
       score
FROM   people  p
WHERE  (SELECT COUNT(*) 
        FROM   people p2
        WHERE  p2.score  > p.score 
       ) <=4 

I believe this should work in most places.