May 25, 2010 at 9:41 am
Hi,
I need to calculate a rank column. Just assume following result and my desire rank column.
Please suggest a solution with Ranking Functions.
Thanks
ID | Data | Rank
------------------
1 1 1
2 1 1
3 3 2
4 3 2
5 2 3
6 5 4
7 9 5
8 9 5
9 1 6
May 25, 2010 at 10:39 am
I suppose that the rank is based on the fact that the sequence on column "Data" continues without changes.
If this is correct, it can be done this way:
DECLARE @RankTest TABLE (
id int,
Data int
)
INSERT INTO @RankTest
SELECT 1, 1 UNION ALL
SELECT 2, 1 UNION ALL
SELECT 3, 3 UNION ALL
SELECT 4, 3 UNION ALL
SELECT 5, 2 UNION ALL
SELECT 6, 5 UNION ALL
SELECT 7, 9 UNION ALL
SELECT 8, 9 UNION ALL
SELECT 9, 1
;WITH Test AS (
SELECT *
FROM @RankTest AS A
OUTER APPLY (
SELECT TOP 1 Data AS PrevData, Id AS PrevId
FROM @RankTest
WHERE id < A.id
ORDER BY id DESC
) AS B
)
SELECT id, Data, Rank = DENSE_RANK() OVER(ORDER BY CASE PrevData WHEN Data THEN previd ELSE id END)
FROM Test
ORDER BY id
Hope this helps
Gianluca
-- Gianluca Sartori
May 25, 2010 at 10:51 am
Or with the row difference method:
-- *** Test Data ***
DECLARE @t TABLE
(
ID int NOT NULL
,Data int NOT NULL
)
INSERT INTO @t
SELECT 1, 1
UNION ALL SELECT 2, 1
UNION ALL SELECT 3, 3
UNION ALL SELECT 4, 3
UNION ALL SELECT 5, 2
UNION ALL SELECT 6, 5
UNION ALL SELECT 7, 9
UNION ALL SELECT 8, 9
UNION ALL SELECT 9, 1
-- *** End Test Data ***
;WITH DataOrder
AS
(
SELECT ID, Data
,ROW_NUMBER() OVER (PARTITION BY Data ORDER BY ID) AS RowNum
FROM @t
)
SELECT ID, Data
,DENSE_RANK() OVER (ORDER BY RowNum - ID DESC, Data DESC) As [Rank]
FROM DataOrder
ORDER BY ID
May 25, 2010 at 11:06 am
THANK YOU ALL
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply