How can I select Distinct Values of nvarchar datatype and give them a Rank in this scenario?

Question

I am running SQL Server 2012 and I have a table named DateDimension in my database. My DateDimension Table is a table with 2 columns: Column date contain daily dates running from '2014-01-01' to '2019-12-31' and Column WeekGroup is of nvarchar(100) datatype.

An extract is given below:

date         WeekGroup
2014-01-01   Wk 30 Dec 2013-05 Jan 2014
2014-01-02   Wk 30 Dec 2013-05 Jan 2014
2014-01-03   Wk 30 Dec 2013-05 Jan 2014
2014-01-04   Wk 30 Dec 2013-05 Jan 2014
2014-01-05   Wk 30 Dec 2013-05 Jan 2014
2014-01-06   Wk 06 Jan 2014-12 Jan 2014
...

I now have the following query:

SELECT
[date],
[WeekGroup],
RANK() OVER (ORDER BY [date] ASC) AS 'Rank'

FROM DateDimension

WHERE date BETWEEN '2014-01-01' AND '2019-12-31'

ORDER BY [date] ASC

My output is as follows:

date         WeekGroup                     Rank
2014-01-01   Wk 30 Dec 2013-05 Jan 2014     1
2014-01-02   Wk 30 Dec 2013-05 Jan 2014     2
2014-01-03   Wk 30 Dec 2013-05 Jan 2014     3
2014-01-04   Wk 30 Dec 2013-05 Jan 2014     4
2014-01-05   Wk 30 Dec 2013-05 Jan 2014     5
2014-01-06   Wk 06 Jan 2014-12 Jan 2014     6
...

I would like to fine tune the above query so that I get the following output, with distinct WeekGroup and a corresponding Rank:

WeekGroup                     Rank
Wk 30 Dec 2013-05 Jan 2014     1
Wk 06 Jan 2014-12 Jan 2014     2
...

How can I do this on an nvarchar variable (WeekGroup)?

Answer 1

Leave date out and aggregate by the week group:

SELECT WeekGroup,
       RANK() OVER (ORDER BY MIN([date]) ASC) AS [Rank]
FROM DateDimension
WHERE date BETWEEN '2014-01-01' AND '2019-12-31'
GROUP BY WeekGroup
ORDER BY MIN([date]) ASC;