CODEWITHSUNDEEP
mysqlsqloracle-databaset-sqlpostgresql
ene.andrei
ene.andrei

Reputation: 187

SQL query for top 5 results without the use of LIMIT/ROWNUM/TOP

How do you select the top 5 entries from an ordered set of results without the usage of LIMIT/ROWNUM/TOP depending on the DBMS?

Upvotes: 1

Views: 4357

Answers (1)

user330315
user330315

Reputation:

Standard ANSI SQL solution:

SELECT *
FROM ( 
    SELECT col1, 
           col2,
           row_number() over (order by some_col) as rn
    FROM the_table
) t
WHERE rn <= 5

Works in Oracle, PostgreSQL, DB2, SQL Server, Sybase, Teradata and the upcoming Firebird 3.0 but not in MySQL as it still doesn't support windowing functions.

Upvotes: 9

Related Questions