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