CODEWITHSUNDEEP
mysqlrankingrank
Kris
Kris

Reputation: 75

MySQL: use group's earliest date to determine each row's rank

I would like to rank rows based on their earliest date. However if a row's sibling (row within the same group) has a higher ranking this would affect their ranking.

id  | group | date 
1     11      2016-12-1
2     11      2016-01-1
3     22      2016-02-1
4     22      2016-05-1
5     3       2016-04-1
6     3       2016-06-1

Currently:

SELECT *, @curRank := @curRank + 1 AS rank 
FROM table 
SELECT @curRank := 0) r
ORDER BY date ASC

id  | group | date       | rank
2     11      2016-01-1    1
3     22      2016-02-1    2
5     3       2016-04-1    3
4     22      2016-05-1    4
6     3       2016-06-1    5
1     11      2016-12-1    6

I need to achieve 

id  | group | date       | rank
2     11      2016-01-1    1
1     11      2016-12-1    6
3     22      2016-02-1    2
4     22      2016-05-1    4
5     3       2016-04-1    3
6     3       2016-06-1    5

Upvotes: 0

Views: 1120

Answers (2)

ivanm
ivanm

Reputation: 138

I renamed your table to test1 and the group column to groupp because group is a reserved word.

The following query will give you the desired result

SELECT t1.*
FROM (SELECT id, groupp, min(date), @rownum := @rownum + 1 as rank
      FROM test1, (SELECT @rownum := 0) r
      GROUP BY groupp
      ORDER BY date ASC) qry
INNER JOIN test1 t1 on t1.groupp = qry.groupp
ORDER BY qry.rank, t1.date asc;

The query marked with qry will get you the record with minimum date for each groupp. The query t1 will get you all the records from the table. When you join these two on column groupp you will basically get all the records from the table with the appropriate rank based on the result from the first query. Then you just order by rank and date.

I tested the query and it returns the desired result with sql fiddle

http://sqlfiddle.com/#!9/90dee/1

Upvotes: 1

xQbert
xQbert

Reputation: 35333

Seems like you could just nest your existing query as a inline view and run the ranking again.

UNTESTED:

SELECT Z.*, @newRank := @newRank + 1 as NewRank
FROM (SELECT *, @curRank := @curRank + 1 AS rank 
      FROM foo
      CROSS JOIN (SELECT @curRank := 0) r
      ORDER BY mdate ASC) Z
CROSS JOIN (Select @newRank :=0)
ORDER BY rank, mdate

Upvotes: 0

Related Questions