March 1, 2005 at 11:08 pm
I am new in developing . I need help from you guys . I want to retreive row from a table from n row to m row. example 20 th row to 30th row without using between command. Thanks in advance. |
March 2, 2005 at 12:13 am
u can set the rownum like in oracle. follwing article will give u some ideas
My Blog:
March 3, 2005 at 2:04 am
How is the table ordered? Without knowing this you can't know which are rows n & m.
If you just want all rows between n and m in an unordered set (can't think why you would) then its pretty difficult to do. If you are using SQL Server 2005 Beta (which I presume you are because this is a SQL Server 2005 discussion forum) you can use the ROW_NUMBER() function which is designed to solve this kind of problem.
Find out more about ROW_NUMBER() at http://blogs.conchango.com/jamiethomson/archive/2005/02/16/1025.aspx
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
March 3, 2005 at 5:25 am
Hi all,
In the BOL for SQL Server 2005 Beta 2 (Yukon) the given example is
USE AdventureWorks
GO
WITH OrderedOrders AS
(SELECT SalesOrderID, OrderDate,
ROW_NUMBER() OVER (order by OrderDate)as RowNumber
FROM Sales.SalesOrderHeader )
SELECT *
FROM OrderedOrders
WHERE RowNumber between 50 and 60
But when I first meet this question, I solved in the following method
select * from (
select row_number() over(order by cityname) rid, * from Cities
) x where rid between 5 and 10
-- order by CityId
Eralper
March 3, 2005 at 5:51 am
Eralper,
Did you copy that straight out of BOL? I didn't think that syntax would work because it is a CTE and requires a semi-colon after:
SELECT *
FROM OrderedOrders
WHERE RowNumber between 50 and 60
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
March 3, 2005 at 6:09 am
Hi Jamie,
I copied straight out of the BOL.
I tried, it is working.
About the semi-colon, we need it before the WITH, if there is an other sql statement then the CTE expression. But we don't need it after the CTE
Since we have the GO we do not need the semi-colon before WITH
USE AdventureWorks
GO
WITH OrderedOrders AS
(SELECT SalesOrderID, OrderDate,
ROW_NUMBER() OVER (order by OrderDate)as RowNumber
FROM Sales.SalesOrderHeader )
SELECT *
FROM OrderedOrders
WHERE RowNumber between 50 and 60
Eralper
March 3, 2005 at 6:12 am
OK, I've been misinformed then.
Thanks for the info.
Sorry for the digression.
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply