January 22, 2009 at 5:15 am
Hello all.
OK, I'm trying to write query which will return the rows 2-10 from a table. Essentially the top ten, minus the top result. I'm at a loss as to how to do this (if indeed it can be). Any pointers would be greatly appreciated.
Thanks for reading people.
January 22, 2009 at 5:26 am
...ROW_NUMBER() is what you are looking for
--Ramesh
January 22, 2009 at 5:26 am
Would something like this work??
SELECT TOP 9 *
FROM mytable a
WHERE a.mycolumn IN (SELECT TOP 10 *
FROM mytable b
ORDER BY mycolumn)
ORDER BY mycolumn DESC
January 22, 2009 at 5:29 am
sam.bailey (1/22/2009)
Would something like this work??SELECT TOP 9 *
FROM mytable a
WHERE a.mycolumn IN (SELECT TOP 10 *
FROM mytable b
ORDER BY mycolumn)
ORDER BY mycolumn DESC
Hi sam, cheers for the reply. Looking at that code, would it not be the case that that would bring the top 9 back rather than rows 2 to 10?
Cheers.
January 22, 2009 at 5:33 am
You can change the numbers in the TOP 🙂 Its just to give you an idea of where to look... good luck
January 22, 2009 at 5:39 am
gavin.duncan (1/22/2009)
OK, I'm trying to write query which will return the rows 2-10 from a table. Essentially the top ten, minus the top result.
Row_Number is what you're looking for. Rows 2-10 ordered by what column?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 22, 2009 at 5:41 am
hi sam.
yeah i can see where your coming from. However i still don't understand this. The nested sub-query is going to bring you back the top 10 which is fair enough. However the TOP 9 is going to bring you the top 9 form the 10 returned by the sub query, i don't understand how that's going to omit the top result, regardless of what you set it as.
Thanks again mate.
January 22, 2009 at 5:46 am
GilaMonster (1/22/2009)
gavin.duncan (1/22/2009)
OK, I'm trying to write query which will return the rows 2-10 from a table. Essentially the top ten, minus the top result.Row_Number is what you're looking for. Rows 2-10 ordered by what column?
Hi mate, cheers for the reply.
The table has a user-defined column called Sequence, its to be ordered by that. Im having a look at Row_Number as we speak.
Thanks.
January 22, 2009 at 5:48 am
The trick is in the order by! One ascending & one descending...
January 22, 2009 at 5:52 am
sam.bailey (1/22/2009)
The trick is in the order by! One ascending & one descending...
Ahhh ok mate, sorry i didn't notice one was asc and the other desc.
Is there any difference in terms of performance and reliability between this method and ROW_NUMBER do you know?
Thanks for all the replies guys.
January 22, 2009 at 6:13 am
gavin.duncan (1/22/2009)
The table has a user-defined column called Sequence, its to be ordered by that. Im having a look at Row_Number as we speak.
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (Order By Sequence) as RowNo FROM SomeTable) Sub
WHERE Sub between 2 and 10
As for perf difference, try them both and see. My guess is that the row number will be faster on large resultsets because there's only one sort involved, not two as in the TOP methos.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 22, 2009 at 7:22 am
Brilliant.
Thanks very much for your help GilaMonster! Top lady!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply