Created
December 19, 2012 03:09
-
-
Save bdargan/4334057 to your computer and use it in GitHub Desktop.
Select top N records from various databases
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Returning only the first N records in a SQL query differs quite a bit between database platforms. Here's some samples: | |
Microsoft SQL Server | |
SELECT TOP 10 column FROM table | |
PostgreSQL and MySQL | |
SELECT column FROM table | |
LIMIT 10 | |
Oracle | |
SELECT column FROM table | |
WHERE ROWNUM <= 10 | |
Sybase | |
SET rowcount 10 | |
SELECT column FROM table | |
Firebird | |
SELECT FIRST 10 column | |
FROM table | |
Due to these differences if you want to keep your code database independent you should use the maxrows attribute in the cfquery tag in ColdFusion. The tradeoffs to database independance is performance, I would expect maxrows to be slower than specifying the rows in the SQL. | |
<cfquery datasource="#ds#" maxrows="10"> | |
SELECT column FROM table | |
</cfquery> | |
PostgreSQL and MySQL have a cool feature that will let you return an arbitrary range of rows (eg return rows 10-20). This is very handy for displaying pages of records: | |
SELECT column FROM table | |
LIMIT 10 OFFSET 20 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment