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

No. The syntax is different.

You may, however, create views:

/* Oracle */

CREATE VIEW v_table
AS
SELECT  *
FROM    (
        SELECT  *
        FROM    table
        ORDER BY
                column
        )
WHERE   rownum <= n

/* MySQL */

CREATE VIEW v_table
AS
SELECT  *
FROM    table
ORDER BY
        column
LIMIT   n

Answer 2

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.

Answer 3

Starting with MySQL 8, you can use ROW_NUMBER() filtering to get the semantics of LIMIT (MySQL) or FETCH (Oracle) in a uniform, standards compliant syntax:

SELECT t.a, t.b, t.c, t.o
FROM (
  SELECT a, b, c, o, ROW_NUMBER() OVER (ORDER BY o)
  FROM x
) t
WHERE rn <= :limit
ORDER BY o

But this is likely to be less efficient than using the vendor specific syntax, so if you have some means of abstracting over LIMIT and FETCH (e.g. using an ORM like jOOQ or Hibernate, or even some templating language), that should be preferred.

Answer 4

I don't think that's possible even just between mysql and mssql. I do an option for simulating such behaviour though:

1. create views that have an auto incremented int column; say 'PagingHelperID'
2. write queries like: SELECT columns FROM viewname WHERE PagingHelperID BETWEEN startindex AND stopindex

This will make ordering difficult, you will need different views for every order in which you intend to retreive data.

You could also "rewrite" your sql on the fly when querying depending on the database and define your own method for the rewriter, but I don't think there is any "good" way to do this.

Answer 5

If there is a unique key on the table yes...

Select * From Table O
Where (Select Count(*) From Table I
       Where [UniqueKeyValue] < O.UniqueKeyValue)  < N

You can substitute your own criteria if you want the "Top" definition to be based on some other logic than on the unique key...

EDIT: If the "sort" that defines the meaning of "Top" is based on a non-unique column, or set of columns, then you can still use this, but you can't guarantee you will be able to get exactly N records out...

  Select * From Table O
  Where (Select Count(*) From Table I
         Where nonUniqueCol < O.nonUniqueCol) < 10

If records 8, 9, 10, 11, and 12 all have the same value in [nonUniqueCol], then the query will either only generate 7 records, (with '<') ... , or 12 (if you use '<=')

NOTE: As this involves a correlated sub-query, the performance can be an issue for very large tables...

Answer 6

The big problem, after looking this over, is that MySQL isn't ISO SQL:2003 compliant. If it was, you'd have these handy windowing functions:

SELECT * from
(   SELECT
    RANK() OVER (ORDER BY <blah>) AS ranking,
    <rest of columns here>,
    FROM <table>
)
WHERE ranking <= <N>

Alas, MySQL (and others that mimic it's behavior, eg SQLite), do not, hence the whole limiting issue.

Check out this snippet from Wikipedia (http://en.wikipedia.org/wiki/Window_function_(SQL)#Limiting_result_rows)