Could there be a SQL agnostic SQL query to retrieve the top N rows?

Question

We want to select the top N rows using an SQL query. The target database may be Oracle or MySQL. Is there an elegant approach to this? (Needless to say, we are dealing with sorted data here.)

Answer 1

To get the 5 best counters 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 2

No. The syntax is different.

However, you can 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 3

, mysql mssql. , :

• int; 'PagingHelperID'
• : SELECT columns FROM viewname WHERE PagingHelperID BETWEEN startindex AND stopindex

, , .

"" sql " " , , "" .

Answer 4

, ...

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

, , "" - , ...

EDIT: "", "", , , , N ...

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

8, 9, 10, 11 12 [nonUniqueCol], 7 ( '<')... 12 ( '< =')

. , ...

Answer 5

, . , . ANSI SQL LIMIT MYSQL?

Answer 6

, , , MySQL ISO SQL: 2003. , :

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

, MySQL ( , , SQLite), , , .

(http://en.wikipedia.org/wiki/Window_function_(SQL)#Limiting_result_rows)