Returning TOP N Records

ytuwlg

 
Returning TOP N Records

<div class="entryBody">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 tablePostgreSQL and MySQL
SELECT column FROM tableLIMIT 10Oracle
SELECT column FROM tableWHERE ROWNUM <= 10Sybase
SET rowcount 10SELECT column FROM tableFirebird
SELECT FIRST 10 column FROM tableDue to these differences if you want to keep your code database independent you should use the maxrows attribute in the cfquerytag in ColdFusion. The tradeoffs to database independance isperformance, I would expect maxrows to be slower than specifying therows 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 anarbitrary range of rows (eg return rows 10-20). This is very handy fordisplaying pages of records:
SELECT column FROM tableLIMIT 10 OFFSET 20The above query will return rows 20-30