homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

sql query for retriving the only 10 records per page
performance optimization

 12:40 pm on Sep 9, 2008 (gmt 0)

Hi friends...
I want to run sql select query command to retrive the 10 records
on each page based on the page number.
for example if pagenumber = 2 then I want to retrive records only from 20 to 30.
Right now what I am doing is that I am selecting the all records and for displaying records from 20 to 30, first I am looping to make
cursor to read up to 20th position then from that I am displaying the records.

select * from table

int loop=pageid*10

while loop
end while

Problem is that for large records it will take lots of time to run this query..
Please suggusts me some better way to do this....



 3:23 pm on Sep 9, 2008 (gmt 0)

It depends on your DB. I believe MySQL allows something like this.


 7:21 pm on Sep 9, 2008 (gmt 0)

If it is MYSQL Database then you can use the Limit Keyword easily for this

for example
select * from table limit 10

// Means record 0 to 10 //

thats so normal right ? yea but this is the trick

select * from table limit 10,20

that means select 20 records after record 10, i think ACCESS database doesnt allow two parameters for the LIMIT keyword, not sure about SQL Server.

[edited by: Anyango at 7:22 pm (utc) on Sep. 9, 2008]


 8:49 pm on Sep 9, 2008 (gmt 0)

Nope- not in MS SQL. There are some kludgy workarounds, but they break down with any complicated queries.


 3:20 am on Sep 10, 2008 (gmt 0)

SELECT * FROM mytable ORDER BY something LIMIT 10, OFFSET 20

returns rows 10 - 20


 5:14 am on Sep 10, 2008 (gmt 0)

I am using MSSQL will it work for that also?


 3:30 pm on Sep 10, 2008 (gmt 0)

I am using MSSQL will it work for that also?

As I previously mentioned, no.

See the following for a kludgy workaround (works with the Pubs database):
select * from (
select top 10 emp_id,lname,fname from (
select top 30 emp_id,lname,fname
from employee
order by lname asc
) as newtbl order by lname desc
) as newtbl2 order by lname asc


 4:09 pm on Sep 10, 2008 (gmt 0)

There's a good article on paging with SQL Server at aspfaq [databases.aspfaq.com].

(The article covers Classic ASP, but the SQL bits apply to .Net and the stored procedure at the end may be a good solution)


 6:01 am on Sep 12, 2008 (gmt 0)

SQL 2005 has paging built into it, FYI.

We use 2005, but haven't moved to paging yet, we use a simple temp table to order the results and then join that table with the ten results we need.

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved