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

Need Help With SQL?

SQL & Database Training Classes - Instructor Led Online Training Classes for SQL, MySQL, Oracle, and SQL Server.

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
Top 10 Reserved SQL Keywords - October 28, 2008
INFORMATION_SCHEMA Support in MySQL, PostgreSQL - February 18, 2008

36 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:

Hello,

How can we find only last updated row from multiple rows with last updated column.

On 01/03/2008 at 4:50:49 AM MST meenu wrote:

hello, how can i get the highest three records in employee table on the behalf of salary field

On 02/05/2008 at 11:54:33 AM MST Durgaprasad wrote:

i need query for how to get top 10 records in table in sql server.plz help me

On 02/15/2008 at 9:23:34 PM MST uday wrote:

i need to get top3 and bottom 3 records from a table in mssql.

On 03/20/2008 at 4:35:09 AM MST Ash wrote:

I need a Query to return 11 - 20 records in DB2.

On 04/21/2008 at 3:10:08 AM MDT ali wrote:

Also I need above sql foe db2 'I need a Query to return 11 - 20 records in DB2.' DB2 version 8..

On 05/30/2008 at 7:36:51 AM MDT Michele Mottini wrote:

The Oracle version does not work as the Microsoft SQL one if the result of the select is sorted: MS returns the first n AFTER the sort, Oracle returns the first n BEFORE the sort.

On 06/01/2008 at 11:06:02 PM MDT kiran wrote:

thnx buddy

On 08/08/2008 at 7:17:31 AM MDT Uma shankar wrote:

Thank you giving nice suggestion

On 09/15/2008 at 11:30:25 PM MDT Vaishali wrote:

i'm using mysql 4. at the time of retriving data from table it takes 12-13 min even if i'm using limit command it takes same time thanks in advance

On 09/23/2008 at 12:22:28 AM MDT Vaishali Saste wrote:

When i used the query SELECT TOP 10 column FROM table in SQL Server 2005 it showed me error as Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'column'

Please tell me what to do....

On 10/15/2008 at 7:01:22 AM MDT Boopathi wrote:

below is the solution in mssql 2000 get the records from 11 to 20 rows

SELECT TOP 10 * FROM tblname where title_id NOT IN (SELECT TOP 10 title_id FROM tblname ORDER BY title_id)ORDER BY title_id

here title_id is auto increment coloum, so u have to create table with auto increment coloum..

On 10/16/2008 at 9:43:01 PM MDT yogesh chaudhari wrote:

i have some records in this id is column which is forgein key i want top 10 record on every id is order by date

On 10/28/2008 at 12:29:00 PM MST Artur Dionisio wrote:

thanks a lot. this page helped me so much on my project about convertion SQL between databases.

On 11/02/2008 at 11:23:53 PM MST Mahi wrote:

Hi I am getting the following error when I use the methods query.setMaxResults(currentPageNumber); query.setFirstResult(currentPageNumber.intValue() * pageSize.intValue());

In DAO. Caused by: com.jnetdirect.jsql.m: Incorrect syntax near the keyword 'top'.

The records are retrieved from a stored procedure..

My requirement in to display 3 records per page. Can I use the above methods to retrieve the records from a stored procedure? If not please suggest me another way of getting the records..

Thanks Mahi

On 11/04/2008 at 10:34:57 AM MST ml wrote:

How do I get the top n percent of a dataset in mysql?

On 11/15/2008 at 4:50:38 AM MST munzir wrote:

You said: "The above query will return rows 20-30" but I guess you mean rows 21-30 which are 10 records after all.

On 11/23/2008 at 8:04:15 PM MST Om wrote:

Thanks buddy, useful article

On 11/24/2008 at 12:11:17 PM MST Serg wrote:

Hi, I'm using Sybase and Iwant to retutn only the first row from a subquery. ROWCOUNT does't work fine, because ROWCOUNT affects all the queries, and I just want afeect the subquery... Any idea=

On 12/19/2008 at 11:31:56 AM MST gsvinothkanna@gmail.com wrote:

hi... i want 2 know the extract intermediate records... if i using the above query the show ORA-00936 error...

On 05/07/2009 at 8:45:06 AM MDT sarmadi wrote:

How should I be able to select first 10 records, then next 10 and so on until the records exist? I am using Sql server 2000. Urgently required. Thanks

On 05/08/2009 at 2:18:22 PM MDT Florin Negoita wrote:

Thanks Pete. Helped me with a CFC that's querying MSSQL and Sybase.