July 22, 2010 at 10:24 pm
please send the ways of writing this like top or row_number.
Malleswarareddy
I.T.Analyst
MCITP(70-451)
July 22, 2010 at 10:39 pm
Since you know that this can be done using ROW_NUMBER(), try some code yourself and if you are stuck, then come back with proper test data and the script with which you are stuck. We can help you then.
With this limited information i am sure not many will even bother to help you out.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 22, 2010 at 10:50 pm
I know that but some one of friend told that its take more time to execute.that why i posted if there is another way which executing faster than using row_number
Malleswarareddy
I.T.Analyst
MCITP(70-451)
July 22, 2010 at 10:57 pm
You can use this method:
WITH Reservation AS
(
SELECT ResId,GuestName,
ROW_NUMBER() OVER (ORDER BY ResId) AS 'RowNumber'
FROM ResInfmas
)
SELECT *
FROM Reservation
WHERE RowNumber BETWEEN 5 AND 10
---
Thinking is the hardest work there is, which is the probable reason so few engage in it.
Sunitha
😎
July 22, 2010 at 11:06 pm
malleswarareddy_m (7/22/2010)
I know that but some one of friend told that its take more time to execute.that why i posted if there is another way which executing faster than using row_number
We cannot say for certain that some method is better than the other for your problem. It is certainly going to require an ORDERING whatever query you write and that will be the one aspect taking a lot of time. One possible solution you might consider is to add a Clustered Index on the ORDER BY Column( if you already don't have one on the table ). This will keep the data in Ordered manner and explicit ordering will not be necessary, which will in turn improve the performance.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 23, 2010 at 4:57 am
You've been asked to provide some DDL and sample data. Since all we have is a generic problem, here is a generic solution.
;WITH CTE AS
(
SELECT *,
RN = ROW_NUMBER() OVER (PARTITION BY UnknownColumns ORDER BY MoreUnknownColumns)
FROM Unknown.TableName
)
SELECT *
FROM CTE
WHERE RN BETWEEN 5 AND 10
Edit: Whoops! I now see that sunitkrishna has already posted an identical solution.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 23, 2010 at 5:41 am
malleswarareddy_m (7/22/2010)
I know that but some one of friend told that its take more time to execute.that why i posted if there is another way which executing faster than using row_number
If you want something else than using row_number you can try select result of you query into temp table adding indentity column, then select from it. You may consider adding clustered index on it as well. something like:
select identity(int,1,1) as rid, * into #res
from ( whole your current query without row_number) q
order by [whetever columns]
create unique clustered index ix_#res on #res(rid)
Then select from #res...
The above method may be faster in some cases - test it
July 23, 2010 at 1:11 pm
malleswarareddy_m (7/22/2010)
I know that but some one of friend told that its take more time to execute.that why i posted if there is another way which executing faster than using row_number
I don't think you will find anything that is faster. However you do it you're going to need a method to order your records and ROW_NUMBER is just as fast as any other.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply