MYSQL Rank based on multiple columns

Question

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

You don't say what your data looks like, or what you have tried for yourself. You will not get any help without those two steps being taken. — miken32
– miken32, Commented Dec 16, 2015 at 21:38
My apologies, I'm fairly new to the discussion environment of stackoverflow should have reviewed the guidelines. Please see the edited post. Thank you! — asyad
– asyad, Commented Dec 16, 2015 at 22:51
You could consider upgrading to MySQL 8 (now GA) which supports window functions - making this and similar needs so much easier to achieve — Paul Maxwell
– Paul Maxwell, Commented Oct 11, 2018 at 3:18

Paul Maxwell · Accepted Answer · 2018-10-11 03:38:43Z

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 |

Collectives™ on Stack Overflow

MYSQL Rank based on multiple columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related