I'm in the process of generalizing a Django DB replication app and it uses the statement:
SELECT %s FROM %s LIMIT 1
to fetch 1 row and use the Python DBAPI to describe the fields, it works fine with ORACLE and MySQL but, how cross platform is the LIMIT statement?
The SQL LIMIT clause constrains the number of rows returned by a SELECT statement. For Microsoft databases like SQL Server or MSAccess, you can use the SELECT TOP statement to limit your results, which is Microsoft's proprietary equivalent to the SELECT LIMIT statement.
The LIMIT clause picks the first row in the result set sorted randomly.
The LIMIT clause is used to specify the number of records to return. The LIMIT clause is useful on large tables with thousands of records. Returning a large number of records can impact performance.
The SQL LIMIT clause restricts how many rows are returned from a query. The syntax for the LIMIT clause is: SELECT * FROM table LIMIT X;. X represents how many records you want to retrieve. For example, you can use the LIMIT clause to retrieve the top five players on a leaderboard.
LIMIT
has become quite popular with a variety of Open Source databases, but unfortunately, the fact is that OFFSET
pagination has been about the least standardised SQL feature of them all, having been standardised as late as in SQL:2008.
Until then, the jOOQ user manual page on the LIMIT
clause shows how the various equivalent statements can be formed in each SQL dialect:
-- MySQL, H2, HSQLDB, Postgres, and SQLite
SELECT * FROM BOOK LIMIT 1 OFFSET 2
-- CUBRID supports a MySQL variant of the LIMIT .. OFFSET clause
SELECT * FROM BOOK LIMIT 2, 1
-- Derby, SQL Server 2012, Oracle 12c, SQL:2008 standard
-- Some need a mandatory ORDER BY clause prior to OFFSET
SELECT * FROM BOOK [ ORDER BY ... ] OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY
-- Ingres
SELECT * FROM BOOK OFFSET 2 FETCH FIRST 1 ROWS ONLY
-- Firebird
SELECT * FROM BOOK ROWS 2 TO 3
-- Sybase SQL Anywhere
SELECT TOP 1 ROWS START AT 3 * FROM BOOK
-- DB2 (without OFFSET)
SELECT * FROM BOOK FETCH FIRST 1 ROWS ONLY
-- Sybase ASE, SQL Server 2008 (without OFFSET)
SELECT TOP 1 * FROM BOOK
Now, these were all pretty straight-forward, right? Here comes the nasty part, when you have to emulate them:
-- DB2 (with OFFSET), SQL Server 2008 (with OFFSET),
SELECT * FROM (
SELECT BOOK.*,
ROW_NUMBER() OVER (ORDER BY ID ASC) AS RN
FROM BOOK
) AS X
WHERE RN > 2
AND RN <= 3
-- DB2 (with OFFSET), SQL Server 2008 (with OFFSET)
-- When the original query uses DISTINCT!
SELECT * FROM (
SELECT DISTINCT BOOK.ID, BOOK.TITLE
DENSE_RANK() OVER (ORDER BY ID ASC, TITLE ASC) AS RN
FROM BOOK
) AS X
WHERE RN > 2
AND RN <= 3
-- Oracle 11g and less
SELECT *
FROM (
SELECT b.*, ROWNUM RN
FROM (
SELECT *
FROM BOOK
ORDER BY ID ASC
) b
WHERE ROWNUM <= 3
)
WHERE RN > 2
Read about the ROW_NUMBER()
vs. DENSE_RANK()
rationale here
Choose your poison ;-)
LIMIT
is very far from universal - out of major RDBMS, it's pretty much restricted to MySQL and PostgreSQL. Here is a detailed analysis of how this is done in many other implementations, including MSSQL, Oracle and DB2, as well as in ANSI SQL.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With