Problem with Rank() function

Hello,

I have troubles with this query:

_SELECT _

_id, _

_SUM(repayment) as suma, _

DATEADD(S, -1, DATEADD(mm,DATEDIFF (m,0,date+1,0)) AS obd

RANK() OVER(PARTITION BY id,suma, ORDER BY DATEADD(S, -1, DATEADD(mm,DATEDIFF (m,0,date+1,0)) asc) AS rank

FROM DDV.DBO.DATA

ORDER BY id, obd

which generates this result:

id suma obd rank
1 0 1.1.2014 1
2 0 1.2.2014 2
3 0 1.3.2014 3
4 8 1.4.2014 1
5 0 1.5.2014 4
6 0 1.6.2014 5

I need to order it by id and suma and everytime when there is a different value in a column suma , the RANK should count from number 1 again. Using above mentioned result table - in a row with id=5 the rank should be 1 and not 4.

Could anybody help me with this please??

Thank you!

Hi,

Will Dense_rank() provide a solution to rank().

Regards
Anna

Consumable test data, with dates in ISO format, is always useful!

``````CREATE TABLE #t
(
id int NOT NULL
,suma int NOT NULL
,obd datetime NOT NULL
);
INSERT INTO #t
VALUES (1, 0, '20140101')
,(2, 0, '20140102')
,(3, 0, '20140103')
,(4, 8, '20140104')
,(5, 0, '20140105')
,(6, 0, '20140106');``````

One approach is to use the order difference technique to group consecutive dates where suma remains the same:

``````WITH Grps
AS
(
SELECT *
,DATEDIFF(day, 0, obd)
- ROW_NUMBER() OVER (PARTITION BY suma ORDER BY obd) AS Grp
FROM #t
)
-- select * from Grps
SELECT id, suma, obd
,ROW_NUMBER() OVER (PARTITION BY suma, Grp ORDER BY obd) AS Ranking
FROM Grps
ORDER BY obd;``````

Looking at this again, it seems your dates might be in European format.
If this is the case then alter the code to:

``````WITH Grps
AS
(
SELECT *
,ROW_NUMBER() OVER (ORDER BY obd)
- ROW_NUMBER() OVER (PARTITION BY suma ORDER BY obd) AS Grp
FROM #t
)
-- select * from Grps
SELECT id, suma, obd
,ROW_NUMBER() OVER (PARTITION BY suma, Grp ORDER BY obd) AS Ranking
FROM Grps
ORDER BY obd;``````