CODEWITHSUNDEEP
mysqlrankwindow-functionsrow-number
asyad
asyad

Reputation: 21

MYSQL Rank based on multiple columns

I am trying to create a query in mysql to assign row numbers based on multiple columns. Following is the format in which I need the desired results

    CN   PN  GroupName  WeekTimeReported    rank    desired_rank    

    X   ProjX   A       12/30/2013           1         1    
    X   ProjX   B       12/30/2013           2         1    
    X   ProjX   C       1/6/2014             3         2    
    X   ProjX   D       1/6/2014             4         2    
    Y   ProjY   A       1/13/2014            5         1    
    Y   ProjY   B       1/13/2014            6         1    
    Y   ProjY   C       1/20/2014            7         2    
    Y   ProjY   D       1/20/2014            8         2    
    Z   ProjZ   A       1/27/2014            9         1    
    Z   ProjZ   B       1/27/2014           10         1    
    Z   ProjZ   C       2/3/2014            11         2    
    Z   ProjZ   D       2/3/2014            12         2

I want my result in the desired_rank column format. So for Same CN and PN I want to rank based on the WeekTimeReported (I want to see the no of weeks project went for). Thank you for your help in advance.

Here is the SQLFiddle and query:

SELECT 
 @cn:=ClientName ClientName
,ProjectName
,GroupName
,CAST(WeekTimeReported AS DATE) WeekTimeReported
,@rc:= CASE WHEN @cn=ClientName THEN @rc+1 ELSE 1 END AS rc
,desired_rank
,Hours
FROM sampledata , (select @rc:=0, @cn='') as rc

Upvotes: 2

Views: 1474

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35583

You need to consider all the changes of data for the various columns.

This is what the query looks like in MySQL 8.0

select
*
, dense_rank() over(partition by `ClientName` order by `WeekTimeReported`,`ProjectName`) rnk
from sampledata

ClientName | ProjectName | GroupName | WeekTimeReported | rank | desired_rank | Hours | rnk
:--------- | :---------- | :-------- | :--------------- | ---: | -----------: | ----: | --:
X          | ProjX       | A         | 2013-12-30       |    1 |            1 |     2 |   1
X          | ProjX       | B         | 2013-12-30       |    2 |            1 |    10 |   1
X          | ProjX       | C         | 2014-01-06       |    3 |            2 |     4 |   2
X          | ProjX       | D         | 2014-01-06       |    4 |            2 |     2 |   2
Y          | ProjY       | A         | 2014-01-13       |    5 |            1 |     3 |   1
Y          | ProjY       | B         | 2014-01-13       |    6 |            1 |     1 |   1
Y          | ProjY       | C         | 2014-01-20       |    7 |            2 |     6 |   2
Y          | ProjY       | D         | 2014-01-20       |    8 |            2 |     1 |   2
Z          | ProjZ       | A         | 2014-01-27       |    9 |            1 |     4 |   1
Z          | ProjZ       | B         | 2014-01-27       |   10 |            1 |     1 |   1
Z          | ProjZ       | C         | 2014-02-03       |   11 |            2 |    17 |   2
Z          | ProjZ       | D         | 2014-02-03       |   12 |            2 |    16 |   2

db<>fiddle here

This is a partial answer for older MySQL, which I hope may assist you reaching the goal:

Query 1:

select
rc - rcwk
, d.*
from (
SELECT 
  @rcwk:= CASE WHEN @cnwk=concat(ClientName,WeekTimeReported) THEN @rcwk+1 ELSE 1 END AS rcwk
, @rc:= CASE WHEN @cn=ClientName THEN @rc+1 ELSE 1 END AS rc
, desired_rank
, ClientName
, ProjectName
, GroupName
, CAST(WeekTimeReported AS DATE) WeekTimeReported
, Hours
, @cn:=ClientName
, @cnwk:=concat(ClientName,WeekTimeReported) abc
FROM sampledata 
cross join (select @rc:=0,@rcwk:=0, @cn='',@cnwk='' ) as rc
order by ClientName, ProjectName, WeekTimeReported
  ) d

Results:

| rc - rcwk | rcwk | rc | desired_rank | ClientName | ProjectName | GroupName | WeekTimeReported | Hours | @cn:=ClientName |         abc |
|-----------|------|----|--------------|------------|-------------|-----------|------------------|-------|-----------------|-------------|
|         0 |    1 |  1 |            1 |          X |       ProjX |         A |       2013-12-30 |     2 |               X | X2013-12-30 |
|         0 |    2 |  2 |            1 |          X |       ProjX |         B |       2013-12-30 |    10 |               X | X2013-12-30 |
|         2 |    1 |  3 |            2 |          X |       ProjX |         C |       2014-01-06 |     4 |               X | X2014-01-06 |
|         2 |    2 |  4 |            2 |          X |       ProjX |         D |       2014-01-06 |     2 |               X | X2014-01-06 |
|         0 |    1 |  1 |            1 |          Y |       ProjY |         A |       2014-01-13 |     3 |               Y | Y2014-01-13 |
|         0 |    2 |  2 |            1 |          Y |       ProjY |         B |       2014-01-13 |     1 |               Y | Y2014-01-13 |
|         2 |    1 |  3 |            2 |          Y |       ProjY |         D |       2014-01-20 |     1 |               Y | Y2014-01-20 |
|         2 |    2 |  4 |            2 |          Y |       ProjY |         C |       2014-01-20 |     6 |               Y | Y2014-01-20 |
|         0 |    1 |  1 |            1 |          Z |       ProjZ |         A |       2014-01-27 |     4 |               Z | Z2014-01-27 |
|         0 |    2 |  2 |            1 |          Z |       ProjZ |         B |       2014-01-27 |     1 |               Z | Z2014-01-27 |
|         2 |    1 |  3 |            2 |          Z |       ProjZ |         C |       2014-02-03 |    17 |               Z | Z2014-02-03 |
|         2 |    2 |  4 |            2 |          Z |       ProjZ |         D |       2014-02-03 |    16 |               Z | Z2014-02-03 |

Upvotes: 1

Related Questions