SQL Ranking Rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL Ranking Rows

Author

Topic

ErwinaX
Starting Member

4 Posts

Posted - 2012-02-10 : 17:32:12

I am trying to "re rank" a set of rows.

For example, if I have a table with the keys 1,2,3,4,5,6,7,8,9,10 and I pass in @KeyList as a varchar of comma delimited keys, and an insert point (@Insert_Point) -- I want to put those keys in the insert point in ascending order and rank the other keys accordingly.

ex: "3,4,7" as my list. 2 as my Insertion_Point.

so this would look like "1,3,4,7,2,5,6,8,9,10"
but I would then want to make this list in ascending order again.

In a real application, the keys would have a value associated with them, so in reality the keys are more of a "rank value".

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-10 : 18:39:01

whats the relevance of Insertion_Point if you're arranging it in ascending order afterwards?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ErwinaX
Starting Member

4 Posts

Posted - 2012-02-10 : 18:43:24

Essentially an Upper and Lower Bound.

If I'm simply swapping keys 6 and 7, I know to ignore everything above and below. That was my thought anyway.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-10 : 21:33:21

quote:
Originally posted by ErwinaX

Essentially an Upper and Lower Bound.

If I'm simply swapping keys 6 and 7, I know to ignore everything above and below. That was my thought anyway.

Don't quite understand your point here. Can you show what is the end result ?

KH
[spoiler]Time is always against us[/spoiler]

ErwinaX
Starting Member

4 Posts

Posted - 2012-02-11 : 08:30:42

Sure. [ [url]http://i.imgur.com/RJWpb.jpg[/url] ]

So you start off with ranks 1 through 10, and their actual values, A - J.

If I select rows F,G and move them to row 2. A is ignored, and we re-rank everything up until H, since those rows will not be affected.

I already have everything set up except for the UPDATE itself. As I have it now, the keys that are to be moved (the selected keys) are passed in as a comma delimited list, "split", and then put into a temp table. I also pass in the Rank that we're moving all of these keys to.

I then need to rerank all the moved keys first, then rank all of the keys below the moved ranks up til the MAX rank out of the key list (which I already have).

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-11 : 09:04:50

[code]
-- Create a Sample Table
declare @sample table
(
Rank int,
Value char
)

-- Insert some Sample Data
insert into @sample select 1, 'A'
insert into @sample select 2, 'B'
insert into @sample select 3, 'C'
insert into @sample select 4, 'D'
insert into @sample select 5, 'E'
insert into @sample select 6, 'F'
insert into @sample select 7, 'G'
insert into @sample select 8, 'H'
insert into @sample select 9, 'I'
insert into @sample select 10, 'J'

-- Working Variable
declare @KeyList varchar(10),
@KeyCount int,
@InsPt int

-- Input Variable
select @KeyList = 'F,G',
@InsPt = 3

-- Find number of keys
select @KeyCount = len(@KeyList) - len(replace(@KeyList, ',', '')) + 1

-- Select Query
; with NewRank as
(
select NewRank = case when s.Rank < @InsPt then s.Rank
when s.Value = k.Data then @InsPt + k.RowID -1
else s.Rank + @InsPt + @KeyCount
end,
s.Value
from @sample s
left join dbo.fnParseList(',', @KeyList) k on s.Value = k.Data
)
select Rank = row_number() over (order by NewRank), Value
from NewRank r
order by Rank

-- Update Query to update changes of Rank
; with NewRank as
(
select NewRank = case when s.Rank < @InsPt then s.Rank
when s.Value = k.Data then @InsPt + k.RowID -1
else s.Rank + @InsPt + @KeyCount
end,
s.Value
from @sample s
left join dbo.fnParseList(',', @KeyList) k on s.Value = k.Data
)
update s
set Rank = r.Rank
from @sample s
inner join
(
select Rank = row_number() over (order by NewRank), Value
from NewRank
) r on s.Value = r.Value
where s.Rank <> r.Rank

select *
from @sample
order by Rank
[/code]

Note : using fnParseList from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

KH
[spoiler]Time is always against us[/spoiler]

ErwinaX
Starting Member

4 Posts

Posted - 2012-02-11 : 10:38:16

The case statement looks very similar to a few ideas that I've come up with, so it looks like this is very close to on track.

I took it and played with it a bit, but still haven't been able to get quite what I need. Instead of using fnParseList I already am putting each key into a temp table already.

Though I see you're doing both a SELECT and an UPDATE (are both needed?), using a subquery in the update (which I'm trying to avoid currently), and you're using the data (rather than the key) in one of your CASE statements. Wouldn't this cause issues if the table contains identical "data" results?

Looks really close though thanks! Giving me some ideas to play with (didn't know about ROW_NUMBER())

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources