-3

It's easy to do pagination using mysql or postgresql by using the LIMIT x,y clause. However it is not supported by Oracle or MSSQL. Any insights on why?

I'm not asking for alternatives, I'am aware of them (BTW SQL-SERVER's TOP function doesn't provide the same functionality). I would like to know if there is any technical or licensing reason why the LIMIT x,y clause that it is very useful for web development (pagination) and has a very clean and simple syntax in MySQL and PostgreSQL has complex syntax, sometimes involving subqueries, in other RDBMSs.

Adam Lear
  • 38,111
  • 12
  • 81
  • 101
zad
  • 3,355
  • 2
  • 24
  • 25
  • Because they all implement their own variations of the SQL standard. This is one of the biggest differences between all of them. – John Conde Feb 02 '13 at 23:17
  • I can't answer why, but you can use TOP. – Asad Saeeduddin Feb 02 '13 at 23:17
  • `LIMIT` isn't ANSI standard SQL, and the standard didn't specify any equivalent functionality. IIRC it's recently been added with syntax similar to but not quite the same as PostgreSQL's, but I don't have references close to hand. – Craig Ringer Feb 03 '13 at 03:26
  • 1
    The clauses LIMIT and OFFSET are PostgreSQL-specific syntax, also used by MySQL. The SQL:2008 standard has introduced the clauses OFFSET ... FETCH {FIRST|NEXT} ... for the same functionality - that is supported by PostgreSQL too. – Pavel Stehule Feb 03 '13 at 14:46

1 Answers1

2

MS SQL Server has TOP (n) clause. For Oracle you can use ROWNUM. Example from SQL Server

SELECT TOP 5 * FROM Table

You can use it also with ORDER BY clause as

SELECT TOP 5 * FROM Table ORDER BY col1

from Oracle

SELECT * FROM Table WHERE ROWNUM <= 5

also for Oracle

SELECT * FROM 
(SELECT * FROM Table ORDER BY col1) A WHERE ROWNUM <= 5
Dan Bracuk
  • 20,699
  • 4
  • 26
  • 43
Hamlet Hakobyan
  • 32,965
  • 6
  • 52
  • 68