DENSE_RANK according to particular order

问题:

Hi I have a table of data I want to output the dense_rank of the names starting from the first group of names according to sorted dates order. e.g.

DROP TABLE MyTable
SELECT * INTO MyTable FROM (
  VALUES ('2015-12-23', 'ccc'),('2015-12-21', 'aaa'),
         ('2015-12-20', 'bbb'),('2015-12-22', 'aaa')
) t (date, name)
SELECT DENSE_RANK() OVER (ORDER BY name) rank, * FROM MyTable ORDER BY date

For the query above I got

rank    date    name
2   2015-12-20  bbb
1   2015-12-21  aaa
1   2015-12-22  aaa
3   2015-12-23  ccc

You can see the dates are sorted (good), ranks are assigned to names in group (good), but the ranks do not start from the first group of name, e.g. I want

rank    date    name
1   2015-12-20  bbb
2   2015-12-21  aaa
2   2015-12-22  aaa
3   2015-12-23  ccc

How would you correct the query? If there are multiple working answers, the simplest/shortest one will be picked as the answer. Thanks.

Added:

Thanks @lad2025 and @GiorgosBetsos for clarifying my question. Sorting is straightly according to dates and dates are unique in my case. Names can repeat and appear non-consecutively. So with ('2015-12-24', 'aaa'), the output is

rank    date    name
1   2015-12-20  bbb
2   2015-12-21  aaa
2   2015-12-22  aaa
3   2015-12-23  ccc
4   2015-12-24  aaa

回答1:

You can use:

SELECT DENSE_RANK() OVER (ORDER BY minGrpDate),
       [date], name
FROM (
  SELECT MIN([date]) OVER (PARTITION BY grp) AS minGrpDate,
         [date], name
  FROM (       
    SELECT [date], name,
           ROW_NUMBER() OVER (ORDER BY [date])
           -
           ROW_NUMBER() OVER (PARTITION BY name ORDER BY [date]) AS grp
    FROM mytable) AS t ) AS s
ORDER BY Date

Explanation:

• grp field identifies islands of consecutive records having the same name.
• minGrpDate, which is calculated using grp, is the minimum date of each island.
• Using minGrpDate we can now apply DENSE_RANK() to get required rank.

Note1: The above query handles discontinuities in name field, i.e. the case of non-consecutive fields having the same name.

Note2: The query does not handle the case of different name values sharing the same date value.

Demo here

回答2:

First rank distinct names ordered by date and then join on the table:

;WITH cte AS(SELECT name, ROW_NUMBER() OVER(ORDER BY MIN(date)) rn 
             FROM dbo.MyTable 
             GROUP BY name)
SELECT c.rn, m.date, m.name
FROM cte c
JOIN dbo.MyTable m ON m.name = c.name
ORDER BY m.date