pf » Returning TOP N Records

April 18, 2003

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

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

The above query will return rows 20-30

Permalink | Add Comment |

add to del.icio.us | Tags: sql, mysql, oracle, postgresql, top, limit, rownum

Related Entries

SQL to Select a random row from a database table - September 14, 2005
LIMIT and OFFSET SQL Pagination - August 29, 2005
SQL Reserved Key Words Checker Tool - March 28, 2005
INFORMATION_SCHEMA Support in MySQL, PostgreSQL - February 18, 2008
Cheat Sheet Roundup - Over 30 Cheatsheets for developers - September 1, 2005

26 people found this page useful, what do you think?

Trackbacks

Trackback Address: 59/622926E9A139CCC1080D6AF6EE6A995F

Comments

On 10/18/2005 at 10:23:02 PM MDT Ammar Ibrahim wrote:

MySQL also supports arbitrary range of rows.

e.g. SELECT column FROM table LIMIT 10,20

On 11/17/2005 at 2:50:37 AM MST Bryan wrote:

Thanks for the article, this is EXACTLY what I was looking for!! =D

On 05/12/2006 at 11:49:36 AM MDT Ram wrote:

SELECT column FROM table WHERE ROWNUM <= 10

The above query doesn't work properly in Oracle 9i database

On 05/15/2006 at 1:06:10 AM MDT Anupam wrote:

Thanks a lot for this useful article, IT really helped me.

On 06/02/2006 at 3:31:12 AM MDT Anonymous wrote:

how to use in DB2?

On 07/12/2006 at 11:58:51 AM MDT leo wrote:

Firebird: SELECT FIRST 10 column FROM table

On 07/25/2006 at 10:35:15 AM MDT Doug wrote:

DB2, at end of statement:

FETCH FIRST 10 ROWS ONLY

On 08/10/2006 at 11:35:54 PM MDT venkataraman.L wrote:

how to get top n records with out using limit in mysql

On 09/15/2006 at 1:47:27 AM MDT pachu wrote:

use this for oracle 10g fetch.rows.between(tablename,min,max)

On 11/09/2006 at 3:39:56 AM MST suresh wrote:

how can we get the 20% of the total records?

On 12/05/2006 at 12:48:21 PM MST kuchu wrote:

how can I get top N records in sybase?

On 12/12/2006 at 11:57:05 AM MST Neeraj wrote:

Nice article.. thanks a ton

On 12/12/2006 at 12:03:10 PM MST Neeraj wrote:

For Sybase it is

set rowcount 3 select * from <tablename>

On 04/09/2007 at 6:13:42 AM MDT shashi kumar jha wrote:

Hi all, Every thing is well .but when we are using query for randomly fetching record from large table This Query take more time

"select * from (select * from table order by dbms_random.value) where rownum<=5" pls suggest how to reduce the time taken by this query or tell me alternate way to fetch records randomly from a large table

Thanks & Regards Shashi Kumar Jha 09873061870

On 04/20/2007 at 7:42:58 AM MDT Wes wrote:

Is there a way to have an update sql statement that will only process the top n records and not continue with the rest of the records that match the where criteria?

On 04/30/2007 at 4:28:53 AM MDT Ronak wrote:

Hii, I have a serious problem in sql 2000, I want top records from 11 to 20, in sql express i can use 'except' but it is not working in sql 2000 so please help me

Thanks in advance

On 05/04/2007 at 8:11:41 AM MDT suman wrote:

i want to get records from 11 to 20 in sybase tables pls give reply if ant one know

On 05/19/2007 at 3:57:46 AM MDT ljweko wrote:

Firebird: SELECT FIRST 10 SKIP 50 FROM ...

On 06/21/2007 at 5:16:00 AM MDT Rajesh wrote:

how to get the 2nd record in a table in sqlserver 2000?

On 06/22/2007 at 4:59:56 AM MDT Surender Rawat wrote:

nice article

I was exactly looking the same stuff

On 07/06/2007 at 1:31:46 AM MDT Sentha wrote:

How to select first 10 columns from a table in DB2?

On 10/05/2007 at 11:14:59 AM MDT Surya Bhaskar wrote:

Hi, I have a question,

Select top 10 * from employee

the above statement return top 10 row.

but i want the rows from the table other than the top 10. Can any one help me to get it.. iam using SQL server 2005

Thanks Bhaskar

On 11/10/2007 at 2:12:44 AM MST dipansh wrote:

nice article I want top records from 11 to 20, in sql 2005

On 11/19/2007 at 8:14:52 AM MST nandu wrote:

Hai, this is nandu,So nice of giving information regarding queries in different platforms.I have a doubt that i want to repeat the same record evenly as 2nd,4th,6th etc. How to write query in oracle .

thanks and regards

On 11/22/2007 at 1:35:17 AM MST Smiha wrote:

How to get the records from 11 to 20 rows

On 11/23/2007 at 12:09:57 AM MST dipansh wrote:

below is the solution in mssql 2005

get the records from 11 to 20 rows

select * from (select top 20 ROW_NUMBER() over(order by col1) as rowsno ,* from tab1 where col3 not like '%abcd%') as tab2 where rowsno between '11' and '20'

use top keyword for faster result top n records is always equal or grater then the last limit of between comand its only works in 2005 server

On 11/25/2007 at 3:35:15 AM MST Vivek wrote: