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
Using LIMIT and OFFSET is a great way to do paging with SQL on MySQL or PostgreSQL.
Comments
MySQL also supports arbitrary range of rows. e.g. SELECT column FROM table LIMIT 10,20
Thanks a lot for this useful article, IT really helped me.
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?
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
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
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
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.
Thank you giving nice suggestion
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..
thanks a lot. this page helped me so much on my project about convertion SQL between databases.
How do I get the top n percent of a dataset in mysql?
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=
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
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?
Really i was more helpful. then having small doubt in sql server. what does it mean bulk copy? please help me out!
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
can anyone suggest me the query to fetch top 3 salary in DB2 9. Thanks in advance.
In Sybase ASE you can also use the sentence : SELECT TOP 10 * FROM TableX, and it will return you the specified amount of records
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
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
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
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;
for Oracle to work properly: SELECT * FROM ( SELECT * FROM table_name ORDER BY primary_key_column ) WHERE ROWNUM <= 10;