selecting internal records

Expand Post »

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?

Similar Threads

login,display,selecting data from database querries in PHP
Populating a grid in C#
Image upload with description. in PHP
PHP & 406 Not Acceptable in PHP
Populating Drop Down Menu from database in JSP

krokodajl

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

31 posts
since May 2009

Permalink

Jul 1st, 2009

Re: selecting internal records

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:

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

sknake

Featured Poster

Reputation Points: 1749

Solved Threads: 735

Senior Poster

Offline

3,948 posts
since Feb 2009

Permalink

Jul 15th, 2009

Re: selecting internal records

Click to Expand / Collapse Quote 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:
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

rathnakar

Reputation Points: 9

Solved Threads: 0

Newbie Poster

Offline

5 posts
since Nov 2007

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: Using IF ELSE in script

Next Thread in MS SQL Forum Timeline: SQL2008.AdventureWorks install problem

DaniWeb Message

Cancel Changes

User Name
Password