DENSE_RANK according to specific order

Question

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

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 request above, I received

 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 that the dates are sorted (good), the ranks are assigned to the names in the group (good), but the ranks do not start with the first group of names, for example. 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 do you fix the request? If there are several working answers, the simplest / shortest will be selected as the answer. Thanks.

Added:

Thanks to @ lad2025 and @GiorgosBetsos for clarifying my question. Sorting is done directly according to dates and dates that are unique in my case. Names may be repeated and not displayed sequentially. So with ('2015-12-24', 'aaa') , output

 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 

Answer 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:

• Field
• grp identifies islands of consecutive records that have the same name .
• minGrpDate , which is calculated using grp , is the minimum date for each island.
• Using minGrpDate , we can apply DENSE_RANK() to get the desired rank.

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

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

Demo here

Answer 2

Names of the first rank, sorted by date, and then appended to 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