selecting internal records

[krokodajl]

Hi,

in MS SQL we have TOP some_value clause which means that only some first records are selected. For example:
select top 10 products from table_products

How can I select rows for example from 4th to 15th?

[sknake]

There is no out of the box way to do this with MSSQL but its a common request for web applications so you can show rows 1-10, 11-20, 21-30, etc. on a website. Try googling "MSSQL PAGING" or "MSSQL PAGINATION" and you should come up with a lot of relevant results. There are a number of ways to go about doing this and you will need to choose the one that fits your needs best.

Here is a sample:

With EmployeesCTE
as
(
Select row_number() over (order by Name) as rownum,*
from Employees
)
Select * from EmployeesCTE
Where rownum between 291 and 300
Order by Name

I took that code from: http://forloveofsoftware.blogspot.com/2007/03/paging-in-ms-sql-server-pain-ends-here.html

[rathnakar]

There is no out of the box way to do this with MSSQL but its a common request for web applications so you can show rows 1-10, 11-20, 21-30, etc. on a website. Try googling "MSSQL PAGING" or "MSSQL PAGINATION" and you should come up with a lot of relevant results. There are a number of ways to go about doing this and you will need to choose the one that fits your needs best.

Here is a sample:

With EmployeesCTE
as
(
Select row_number() over (order by Name) as rownum,*
from Employees
)
Select * from EmployeesCTE
Where rownum between 291 and 300
Order by Name

I took that code from: http://forloveofsoftware.blogspot.com/2007/03/paging-in-ms-sql-server-pain-ends-here.html

hi friend,
the above query is useful for only sql server 2005 but not for sqlserver 2000.

i have solution for that

select top 12 * from orders where orderid not in(select top 3 orderid from orders)
u need 4th to 15th records
12 means (15-4)+1=number of records
3 means (4-1)=starting record=removing records

regards,
rathnakar