sql - returning top n records

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
(use order by too for the specific column - harinath)
Due to these differences if you want to keep your code database independent you should use the maxrows attribute in the cfquery tag. The tradeoffs to database independance is performance, I would expect maxrows to be slower than specifying the rows in the SQL.


SELECT column FROM table


PostgreSQL has 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

The above query will return rows 20-30