Pete Freitag Pete Freitag

Returning TOP N Records

databases

Returning only the first N records in a SQL query differs quite a bit between database platforms. For example, you can't say on oracle select top 100. Here's some of the different ways you can return the top 10 records on various database platforms (SQL Server, PostgreSQL, MySQL, etc):

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 independence 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


Like this? Follow me ↯

Returning TOP N Records was first published on April 18, 2003.

If you like reading about sql, mysql, oracle, postgresql, top, limit, or rownum then you might also like:

Comments

MySQL also supports arbitrary range of rows. e.g. SELECT column FROM table LIMIT 10,20
by Ammar Ibrahim on 10/18/2005 at 10:23:02 PM UTC
Thanks a lot for this useful article, IT really helped me.
by Anupam on 05/15/2006 at 1:06:10 AM UTC
how to get top n records with out using limit in mysql
by venkataraman.L on 08/10/2006 at 11:35:54 PM UTC
use this for oracle 10g fetch.rows.between(tablename,min,max)
by pachu on 09/15/2006 at 1:47:27 AM UTC
how can we get the 20% of the total records?
by suresh on 11/09/2006 at 3:39:56 AM UTC
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
by shashi kumar jha on 04/09/2007 at 6:13:42 AM UTC
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
by Ronak on 04/30/2007 at 4:28:53 AM UTC
i want to get records from 11 to 20 in sybase tables pls give reply if ant one know
by suman on 05/04/2007 at 8:11:41 AM UTC
How to select first 10 columns from a table in DB2?
by Sentha on 07/06/2007 at 1:31:46 AM UTC
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
by Surya Bhaskar on 10/05/2007 at 11:14:59 AM UTC
nice article I want top records from 11 to 20, in sql 2005
by dipansh on 11/10/2007 at 2:12:44 AM UTC
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
by nandu on 11/19/2007 at 8:14:52 AM UTC
How to get the records from 11 to 20 rows
by Smiha on 11/22/2007 at 1:35:17 AM UTC
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
by dipansh on 11/23/2007 at 12:09:57 AM UTC
Hello, How can we find only last updated row from multiple rows with last updated column.
by Vivek on 11/25/2007 at 3:35:15 AM UTC
hello, how can i get the highest three records in employee table on the behalf of salary field
by meenu on 01/03/2008 at 4:50:49 AM UTC
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.
by Michele Mottini on 05/30/2008 at 7:36:51 AM UTC
Thank you giving nice suggestion
by Uma shankar on 08/08/2008 at 7:17:31 AM UTC
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....
by Vaishali Saste on 09/23/2008 at 12:22:28 AM UTC
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..
by Boopathi on 10/15/2008 at 7:01:22 AM UTC
thanks a lot. this page helped me so much on my project about convertion SQL between databases.
by Artur Dionisio on 10/28/2008 at 12:29:00 PM UTC
How do I get the top n percent of a dataset in mysql?
by ml on 11/04/2008 at 10:34:57 AM UTC
Thanks buddy, useful article
by Om on 11/23/2008 at 8:04:15 PM UTC
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=
by Serg on 11/24/2008 at 12:11:17 PM UTC
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
by sarmadi on 05/07/2009 at 8:45:06 AM UTC
in 1000 column table i need to select first 500 column ....can any give sql stmt or using cursor in plsql
by samraj on 11/28/2009 at 9:10:23 AM UTC
what is the syntax for to fetch top 10 records in Tandem Non stop SQL?
by Boominathan on 12/02/2009 at 4:43:22 PM UTC
I want to find top 5 values in sql but without using top stetment.how can i do
by manish sinha on 12/26/2009 at 4:57:49 PM UTC
i want to randomly records from database but not reapeted again and again
by ghanchi iliyas on 01/16/2010 at 1:04:45 PM UTC
hi this s not working in MySql so plzz tell me how to get 10 rows out of 100 rows
by pratik on 02/04/2010 at 3:18:25 PM UTC
how can i get the top values limiting the retuning rows to 200.
example: sum(amount) return highest 200 values. anyhelp?
by shunan on 02/20/2010 at 3:35:29 AM UTC
Really i was more helpful. then having small doubt in sql server. what does it mean bulk copy? please help me out!
by Bell on 03/19/2010 at 4:15:10 AM UTC
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,....)
by Vijay Bobba on 05/11/2010 at 11:15:42 AM UTC
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??
by Gyandeep Singh on 06/16/2010 at 2:02:58 AM UTC
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.
by Jaymi on 06/22/2010 at 4:30:31 AM UTC
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.
by Chus on 07/20/2010 at 4:26:23 AM UTC
in mysql is select columns from table limit 10
by CARLOS on 08/04/2010 at 12:57:45 PM UTC
can anyone suggest me the query to fetch top 3 salary in DB2 9. Thanks in advance.
by Alok on 10/11/2010 at 2:39:44 AM UTC
In Sybase ASE you can also use the sentence : SELECT TOP 10 * FROM TableX, and it will return you the specified amount of records
by xyz on 11/10/2010 at 4:25:27 AM UTC
i want to get top 1o integer values from a column of sql table plz reply me
by Prateek Kumar on 03/22/2011 at 8:08:20 PM UTC
i want to get top 1o integer values from a column of sql table
by tahira on 08/22/2011 at 4:00:19 AM UTC
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
by Angeline on 03/08/2012 at 9:08:27 AM UTC
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
by Jim on 02/22/2013 at 6:55:36 AM UTC
fetch records except first 10 records in the table
answer: in sql

select * from (select rownum r,emp.*from emp) where r not between 1 and 10;
by g jagannadham on 07/30/2014 at 1:45:11 AM UTC
for Oracle to work properly: SELECT * FROM ( SELECT * FROM table_name ORDER BY primary_key_column ) WHERE ROWNUM <= 10;
by ray dean on 03/07/2018 at 10:59:39 AM UTC