Returning TOP N Records

April 18, 2003
databases

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



Related Entries

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

Comments

MySQL also supports arbitrary range of rows. e.g. SELECT column FROM table LIMIT 10,20
Thanks for the article, this is EXACTLY what I was looking for!! =D
SELECT column FROM table WHERE ROWNUM <= 10 The above query doesn't work properly in Oracle 9i database
Thanks a lot for this useful article, IT really helped me.
how to use in DB2?
Firebird: SELECT FIRST 10 column FROM table
DB2, at end of statement: FETCH FIRST 10 ROWS ONLY
how to get top n records with out using limit in mysql
use this for oracle 10g fetch.rows.between(tablename,min,max)
how can we get the 20% of the total records?
how can I get top N records in sybase?
Nice article.. thanks a ton
For Sybase it is set rowcount 3 select * from <tablename>
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
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?
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
i want to get records from 11 to 20 in sybase tables pls give reply if ant one know
Firebird: SELECT FIRST 10 SKIP 50 FROM ...
how to get the 2nd record in a table in sqlserver 2000?
nice article I was exactly looking the same stuff
How to select first 10 columns from a table in DB2?
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
nice article I want top records from 11 to 20, in sql 2005
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
How to get the records from 11 to 20 rows
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
Hello, How can we find only last updated row from multiple rows with last updated column.
hello, how can i get the highest three records in employee table on the behalf of salary field
i need query for how to get top 10 records in table in sql server.plz help me
i need to get top3 and bottom 3 records from a table in mssql.
I need a Query to return 11 - 20 records in DB2.
Also I need above sql foe db2 'I need a Query to return 11 - 20 records in DB2.' DB2 version 8..
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.
thnx buddy
Thank you giving nice suggestion
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
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....
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..
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
thanks a lot. this page helped me so much on my project about convertion SQL between databases.
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
How do I get the top n percent of a dataset in mysql?
You said: "The above query will return rows 20-30" but I guess you mean rows 21-30 which are 10 records after all.
Thanks buddy, useful article
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=
hi... i want 2 know the extract intermediate records... if i using the above query the show ORA-00936 error...
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
Thanks Pete. Helped me with a CFC that's querying MSSQL and Sybase.
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
Anyone know how to get the top 20 records in Centura/Gupta SQLBase?
Thanks for sybase query
Thank you for the TOP 10 column - works great for SQL2005
in 1000 column table i need to select first 500 column ....can any give sql stmt or using cursor in plsql
what is the syntax for to fetch top 10 records in Tandem Non stop SQL?
I want to find top 5 values in sql but without using top stetment.how can i do
i want to randomly records from database but not reapeted again and again
hi this s not working in MySql so plzz tell me how to get 10 rows out of 100 rows
how can i get the top values limiting the retuning rows to 200.
example: sum(amount) return highest 200 values. anyhelp?
HOW CAN I GET BOTTOM RECORDS EG: FOR TO DISPLAY TOP 5 RECORDS WE USE TOP
Really i was more helpful. then having small doubt in sql server. what does it mean bulk copy? please help me out!
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?
Anyone know how to do this in Teradata?
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,....)
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??
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.
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.
in mysql is select columns from table limit 10
Thank you
can anyone suggest me the query to fetch top 3 salary in DB2 9. Thanks in advance.
how can i fetch top 2 to 10 records
http://www.miblogspot.com
In Sybase ASE you can also use the sentence : SELECT TOP 10 * FROM TableX, and it will return you the specified amount of records
Thanks it is very helpfull
Crisp and complete article !!!
How about the first rows using relativity?
Thanks Vijay Bobba..
i want to get top 1o integer values from a column of sql table plz reply me
i want to get top 1o integer values from a column of sql table
Anyone know how to do this in Teradata?
Thank you. :)
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
i want select top second one records random ly
i want select top second one records
Really good article.. Helped me alot..
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
Informix:
SELECT FIRST 10 [SKIP 20] column FROM table
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;

Post a Comment




  



Spell Checker by Foundeo

Recent Entries



foundeo


did you hack my cf?