Search code examples
sqlsql-serverdistinctranknvarchar

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


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)?


Solution

  • 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;