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
Tweet
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
Trackbacks
Trackback Address: 59/622926E9A139CCC1080D6AF6EE6A995F
Comments
On 10/19/2005 at 12:23:02 AM EDT Ammar Ibrahim wrote:
1
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:
2
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:
3
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:
4
Thanks a lot for this useful article, IT really helped me.
On 06/02/2006 at 5:31:12 AM EDT Anonymous wrote:
5
how to use in DB2?
On 07/12/2006 at 1:58:51 PM EDT leo wrote:
6
Firebird: SELECT FIRST 10 column FROM table
On 07/25/2006 at 12:35:15 PM EDT Doug wrote:
7
DB2, at end of statement:
FETCH FIRST 10 ROWS ONLY
On 08/11/2006 at 1:35:54 AM EDT venkataraman.L wrote:
8
how to get top n records with out using limit in mysql
On 09/15/2006 at 3:47:27 AM EDT pachu wrote:
9
use this for oracle 10g fetch.rows.between(tablename,min,max)
On 11/09/2006 at 5:39:56 AM EST suresh wrote:
10
how can we get the 20% of the total records?
On 12/05/2006 at 2:48:21 PM EST kuchu wrote:
11
how can I get top N records in sybase?
On 12/12/2006 at 1:57:05 PM EST Neeraj wrote:
12
Nice article.. thanks a ton
On 12/12/2006 at 2:03:10 PM EST Neeraj wrote:
13
For Sybase it is
set rowcount 3 select * from <tablename>
On 04/09/2007 at 8:13:42 AM EDT shashi kumar jha wrote:
14
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:
15
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:
16
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:
17
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:
18
Firebird: SELECT FIRST 10 SKIP 50 FROM ...
On 06/21/2007 at 7:16:00 AM EDT Rajesh wrote:
19
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:
20
nice article
I was exactly looking the same stuff
On 07/06/2007 at 3:31:46 AM EDT Sentha wrote:
21
How to select first 10 columns from a table in DB2?
On 10/05/2007 at 1:14:59 PM EDT Surya Bhaskar wrote:
22
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:
23
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:
24
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:
25
How to get the records from 11 to 20 rows
On 11/23/2007 at 2:09:57 AM EST dipansh wrote:
26
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:
27
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:
28
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:
29
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:
30
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:
31
I need a Query to return 11 - 20 records in DB2.
On 04/21/2008 at 5:10:08 AM EDT ali wrote:
32
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:
33
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:
34
thnx buddy
On 08/08/2008 at 9:17:31 AM EDT Uma shankar wrote:
35
Thank you giving nice suggestion
On 09/16/2008 at 1:30:25 AM EDT Vaishali wrote:
36
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:
37
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:
38
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:
39
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:
40
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:
41
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:
42
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:
43
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:
44
Thanks buddy, useful article
On 11/24/2008 at 2:11:17 PM EST Serg wrote:
45
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:
46
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:
47
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:
48
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:
49
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:
50
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:
51
Thanks for sybase query
On 11/19/2009 at 12:15:44 PM EST Buddy wrote:
52
Thank you for the TOP 10 column - works great for SQL2005
On 11/28/2009 at 11:10:23 AM EST samraj wrote:
53
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:
54
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:
55
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:
56
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:
57
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:
58
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:
59
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:
60
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:
61
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:
62
Anyone know how to do this in Teradata?
On 05/11/2010 at 1:15:42 PM EDT Vijay Bobba wrote:
63
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:
64
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:
65
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:
66
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:
67
in mysql is select columns from table limit 10
Post a Comment
Recent Entries
- Howto Install and Run the Android Emulator
- jQuery UI Autocomple IE 6 Select List z-Index Issues
- Path Traversal Vulnerability Security Hotfix for ColdFusion Released
- Using AntiSamy with ColdFusion
- Writing Secure CFML Slides from CFUnited 2010
- Locking Down ColdFusion Presentation Slides
- Cross Domain Data Theft using CSS
- Using jQuery UI Autocomplete with Hidden ID's
e.g. SELECT column FROM table LIMIT 10,20
The above query doesn't work properly in Oracle 9i database
FETCH FIRST 10 ROWS ONLY
set rowcount 3 select * from <tablename>
"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
Thanks in advance
I was exactly looking the same stuff
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
thanks and regards
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
How can we find only last updated row from multiple rows with last updated column.
Please tell me what to do....
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..
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
select top 1 * from table where colmn not in(select top 1 colmn from table order by colmn) order by colmn
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,....)
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.
