sql query for retriving the only 10 records per page

performance optimization

[pritesh]

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.

Code:
select * from table

int loop=pageid*10

while loop
sqlreader.read()
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....

[LifeinAsia]

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

[Anyango]

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]

[LifeinAsia]

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

[blaketar]

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

returns rows 10 - 20

[pritesh]

I am using MSSQL will it work for that also?

[LifeinAsia]

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

[mattur]

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)

[carguy84]

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.