Returning TOP N Records

April 19, 2003

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

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

Trackbacks

Trackback Address: 59/622926E9A139CCC1080D6AF6EE6A995F

Comments

On 10/19/2005 at 12:23:02 AM EDT Ammar Ibrahim wrote:

MySQL also supports arbitrary range of rows.

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

On 11/17/2005 at 4:50:37 AM EST Bryan wrote:

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

On 05/12/2006 at 1:49:36 PM EDT 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 3:06:10 AM EDT Anupam wrote:

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

On 06/02/2006 at 5:31:12 AM EDT Anonymous wrote:

how to use in DB2?

On 07/12/2006 at 1:58:51 PM EDT leo wrote:

Firebird: SELECT FIRST 10 column FROM table

On 07/25/2006 at 12:35:15 PM EDT Doug wrote:

DB2, at end of statement:

FETCH FIRST 10 ROWS ONLY

On 08/11/2006 at 1:35:54 AM EDT venkataraman.L wrote:

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

On 09/15/2006 at 3:47:27 AM EDT pachu wrote:

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

On 11/09/2006 at 5:39:56 AM EST suresh wrote:

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

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

how can I get top N records in sybase?

On 12/12/2006 at 1:57:05 PM EST Neeraj wrote:

Nice article.. thanks a ton

On 12/12/2006 at 2:03:10 PM EST Neeraj wrote:

For Sybase it is

set rowcount 3 select * from <tablename>

On 04/09/2007 at 8:13:42 AM EDT 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 9:42:58 AM EDT 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 6:28:53 AM EDT 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 10:11:41 AM EDT 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 5:57:46 AM EDT ljweko wrote:

Firebird: SELECT FIRST 10 SKIP 50 FROM ...

On 06/21/2007 at 7:16:00 AM EDT Rajesh wrote:

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

On 06/22/2007 at 6:59:56 AM EDT Surender Rawat wrote:

nice article

I was exactly looking the same stuff

On 07/06/2007 at 3:31:46 AM EDT Sentha wrote:

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

On 10/05/2007 at 1:14:59 PM EDT 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 4:12:44 AM EST dipansh wrote:

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

On 11/19/2007 at 10:14:52 AM EST 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 3:35:17 AM EST Smiha wrote:

How to get the records from 11 to 20 rows

On 11/23/2007 at 2:09:57 AM EST 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 5:35:15 AM EST Vivek wrote:

Hello,

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

On 01/03/2008 at 6:50:49 AM EST 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 1:54:33 PM EST 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 11:23:34 PM EST uday wrote:

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

On 03/20/2008 at 6:35:09 AM EDT Ash wrote:

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

On 04/21/2008 at 5:10:08 AM EDT 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 9:36:51 AM EDT 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/02/2008 at 1:06:02 AM EDT kiran wrote:

thnx buddy

On 08/08/2008 at 9:17:31 AM EDT Uma shankar wrote:

Thank you giving nice suggestion

On 09/16/2008 at 1:30:25 AM EDT 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 2:22:28 AM EDT 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 9:01:22 AM EDT 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 11:43:01 PM EDT 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 2:29:00 PM EDT Artur Dionisio wrote:

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

On 11/03/2008 at 1:23:53 AM EST 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 12:34:57 PM EST ml wrote:

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

On 11/15/2008 at 6:50:38 AM EST 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 10:04:15 PM EST Om wrote:

Thanks buddy, useful article

On 11/24/2008 at 2:11:17 PM EST 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 1:31:56 PM EST 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 10:45:06 AM EDT 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 4:18:22 PM EDT Florin Negoita wrote:

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

On 07/23/2009 at 2:24:31 AM EDT mini wrote:

in order to retrieve only the second row just follow the below query...

select top 1 * from table where colmn not in(select top 1 colmn from table order by colmn) order by colmn

On 10/20/2009 at 11:04:00 AM EDT Mustang wrote:

Anyone know how to get the top 20 records in Centura/Gupta SQLBase?

On 10/29/2009 at 9:01:33 AM EDT Anirudha wrote:

Thanks for sybase query

On 11/19/2009 at 12:15:44 PM EST Buddy wrote:

Thank you for the TOP 10 column - works great for SQL2005

On 11/28/2009 at 11:10:23 AM EST samraj wrote:

in 1000 column table i need to select first 500 column ....can any give sql stmt or using cursor in plsql

On 12/02/2009 at 6:43:22 PM EST Boominathan wrote:

what is the syntax for to fetch top 10 records in Tandem Non stop SQL?

On 12/26/2009 at 6:57:49 PM EST manish sinha wrote:

I want to find top 5 values in sql but without using top stetment.how can i do

On 01/16/2010 at 3:04:45 PM EST ghanchi iliyas wrote:

i want to randomly records from database but not reapeted again and again

On 02/04/2010 at 5:18:25 PM EST pratik wrote:

hi this s not working in MySql so plzz tell me how to get 10 rows out of 100 rows

On 02/20/2010 at 5:35:29 AM EST shunan wrote:

how can i get the top values limiting the retuning rows to 200. example: sum(amount) return highest 200 values. anyhelp?

On 03/11/2010 at 7:27:08 AM EST SWETHA wrote:

HOW CAN I GET BOTTOM RECORDS EG: FOR TO DISPLAY TOP 5 RECORDS WE USE TOP

On 03/19/2010 at 6:15:10 AM EDT Bell wrote:

Really i was more helpful. then having small doubt in sql server. what does it mean bulk copy? please help me out!

On 05/03/2010 at 2:00:54 PM EDT Trevor wrote:

Hi this is great but the problem i am having is. I am using MS Access. I have different customer numbers and for each customer i want to display the first date of transaction plus a consecutive 2 dates. Thes consecutive dates each have two columns with a specific filter criteria. Can anybody help with this query?

On 05/04/2010 at 2:24:04 PM EDT Jonathan wrote:

Anyone know how to do this in Teradata?

On 05/11/2010 at 1:15:42 PM EDT Vijay Bobba wrote:

Tested query in PSQL:

select top <n> * from <tablename> where <id> not in( select top <k> <id> from <tablename> )

for all n = no.of records u need to fetch at a time. and k = multiples of n(eg. n=5; k=0,5,10,15,....)

On 06/16/2010 at 4:02:58 AM EDT Gyandeep Singh wrote:

i have a table called X and one of its column is y which is of datatime datatype now if i have to get the last 50 updates of the table via using the column y what is the Sql server 2005 query??

On 06/22/2010 at 6:30:31 AM EDT Jaymi wrote:

Hi all, I'm using "limit" in postgresql, The query "select column1,column2 from table limit 10 offset 0" and "select column1,column2 from table limit 10 offset 1" returned some same records. Some people said I should use "order by" in that query, I tried, but the result as usual. Why?Any help would be appreciate it.

On 07/20/2010 at 6:26:23 AM EDT Chus wrote:

Sybase also admits SQL Server notattion:

SELECT TOP 10 column FROM table

And it is best than

SET rowcount 10

because rowcount is a param of the connection, and have to reconfigure the connection to set rowcount = 0 for the following queries, , if you do not want to have unexpected results.

On 08/04/2010 at 2:57:45 PM EDT CARLOS wrote:

in mysql is select columns from table limit 10

On 09/14/2010 at 4:29:33 AM EDT kannan wrote:

Thank you

On 10/11/2010 at 4:39:44 AM EDT Alok wrote:

can anyone suggest me the query to fetch top 3 salary in DB2 9. Thanks in advance.

On 10/11/2010 at 10:12:45 AM EDT ram wrote:

how can i fetch top 2 to 10 records http://www.miblogspot.com

On 11/10/2010 at 6:25:27 AM EST xyz wrote:

In Sybase ASE you can also use the sentence : SELECT TOP 10 * FROM TableX, and it will return you the specified amount of records

On 11/12/2010 at 12:52:20 PM EST Musa wrote:

Thanks it is very helpfull

On 12/10/2010 at 6:53:42 AM EST Anonymous wrote:

Crisp and complete article !!!

On 01/28/2011 at 6:40:17 PM EST Hector wrote:

How about the first rows using relativity?

On 02/23/2011 at 2:41:03 AM EST Manisha wrote:

Thanks Vijay Bobba..

On 03/22/2011 at 10:08:20 PM EDT Prateek Kumar wrote:

i want to get top 1o integer values from a column of sql table plz reply me

On 08/22/2011 at 6:00:19 AM EDT tahira wrote:

i want to get top 1o integer values from a column of sql table

On 08/22/2011 at 6:12:20 AM EDT Anonymous wrote:

Anyone know how to do this in Teradata?

On 09/06/2011 at 3:27:23 AM EDT Siva wrote:

Thank you. :)

On 03/08/2012 at 11:08:27 AM EST Angeline wrote:

I have a table with following fields Country and Value in the same table. I need to run a query that can return top 10 from Country A and top 20 from Country B, and top 100 from country C. Can someone help? Thanks in advance

On 05/18/2012 at 5:41:24 AM EDT divya wrote:

i want select top second one records random ly

On 05/18/2012 at 5:42:08 AM EDT divya wrote:

i want select top second one records

On 12/24/2012 at 9:53:57 AM EST Vishakha wrote:

Really good article.. Helped me alot..

On 02/14/2013 at 6:24:27 AM EST rkreddy wrote:

how to retrive top rows from oracle 10g

On 02/22/2013 at 8:55:36 AM EST Jim wrote:

Meanwhile, Firebird supports getting arbitrary rows, too akin to PostGreSQL/MySQL SELECT column FROM table LIMIT 10 OFFSET 20

In Firebird 2 (released a long time ago) and newer, it's SELECT column FROM table ROWS 20 TO 30

On 02/26/2013 at 7:19:25 AM EST ogi wrote:

Informix: SELECT FIRST 10 [SKIP 20] column FROM table

On 04/19/2013 at 8:44:47 AM EDT SAJ wrote:

Same select query returning different order of rows for mysql and postgres please help

Returning TOP N Records

Related Entries

Trackbacks

Comments

Post a Comment

Recent Entries