MS SQL

selecting internal records

Please support our MS SQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: May 2009

Posts: 22

Reputation: krokodajl is an unknown quantity at this point

Solved Threads: 0

krokodajl

Offline

Newbie Poster

selecting internal records

Jul 1st, 2009

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?

•

Join Date: Feb 2009

Posts: 3,168

Reputation: sknake has much to be proud of

Solved Threads: 565

sknake

Offline

.NET Enthusiast

Re: selecting internal records

Jul 1st, 2009

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:

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
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
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.co...ends-here.html

Scott Knake
Custom Software Development
Apex Software, Inc.

•

Join Date: Nov 2007

Posts: 5

Reputation: rathnakar is an unknown quantity at this point

Solved Threads: 0

rathnakar

Offline

Newbie Poster

Re: selecting internal records

Jul 15th, 2009

•

Originally Posted by 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:
Help with Code Tags

sql Syntax (Toggle Plain Text)
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
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.co...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