assign same rank to row with same property in mysql

Question

I'm using the following query to extract a random row from the ones with the minimum date difference within a subgroup (as defined by an ID1,ID2 pair).

It is not working, as the steps that ranks the rows by date difference assigns rank=1 only to the first observation (i.e. even if the second has the same date difference and should receive a rank = 1 too).

Somehow I need to change the WHEN clause to something like...

 CASE 
 WHEN @id1 = t.id1 AND @id2 = t.id2 AND @diff != diff THEN @rownum := @rownum + 1
 ELSE @rownum := 1

but when I do that it doesn't find the diff field...

DROP TABLE IF EXISTS temp4;
    CREATE TABLE temp4 AS
    SELECT x.id1,
           x.id2,
           x.YEAR,
           x.MMDD,
           x.id3,
           x.id3_YEAR,
           x.id3_MMDD,
           x.rk
     FROM (SELECT t.*,
                   ABS(DATEDIFF(CONCAT(CAST(t.id3_YEAR AS CHAR(4)),'-', LEFT(t.id3_MMDD,2),'-',RIGHT(t.id3_MMDD,2)),
                            CONCAT(CAST(t.YEAR AS CHAR(4)),'-', LEFT(t.MMDD,2),'-',RIGHT(t.MMDD,2))))  AS diff,
                   CASE 
                     WHEN @id1 = t.id1 AND @id2 = t.id2 THEN @rownum := @rownum + 1
                     ELSE @rownum := 1
                   END AS rk,
                   @id1 := t.id1,
                   @id2 := t.id2
              FROM temp3 t
              JOIN (SELECT @rownum := 0, @id1  := 0, @id2 := 0) r
          ORDER BY t.id1, t.id2, diff, RAND()) x;

I'm pasting the SQL dump

DROP TABLE IF EXISTS `temp3`;
CREATE TABLE IF NOT EXISTS `temp3` (
  `id1` char(7) NOT NULL,
  `id2` char(7) NOT NULL,
  `YEAR` year(4) NOT NULL,
  `MMDD` char(4) NOT NULL,
  `id3` char(7) NOT NULL,
  `id3_YEAR` year(4) NOT NULL,
  `id3_MMDD` char(4) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


INSERT INTO `temp3` VALUES('1', '2', 1992, '0107', '55', 1991, '0528');
INSERT INTO `temp3` VALUES('1', '2', 1992, '0107', '57', 1991, '0701');
INSERT INTO `temp3` VALUES('1', '3', 1992, '0107', '88', 2000, '0101');
INSERT INTO `temp3` VALUES('1', '3', 1992, '0107', '44', 2000, '0101');

This version is actually really close to what I need

DROP TABLE IF EXISTS temp4;
CREATE TABLE temp4 AS
SELECT
    x.id1,
    x.id2,
    x.YEAR,
    x.MMDD,
    x.id3,
    x.id3_YEAR,
    x.id3_MMDD
FROM
(   SELECT
        t.*,
        @rownum := CASE 
            WHEN @id1 = t.id1 AND @id2 = t.id2 AND @diff != diff THEN @rownum + 1

            ELSE 1
            END AS rk,
        @id1 := t.id1,
        @id2 := t.id2,
        @diff := t.diff
    FROM
    (   SELECT
            t.*,
             ABS(DATEDIFF(CONCAT(CAST(t.id3_YEAR AS CHAR(4)),'-', LEFT(t.id3_MMDD,2),'-',RIGHT(t.id3_MMDD,2)),
             CONCAT(CAST(t.YEAR AS CHAR(4)),'-', LEFT(t.MMDD,2),'-',RIGHT(t.MMDD,2)))) AS diff
        FROM temp3 t
        ORDER BY t.id1, t.id2, diff, RAND()
    ) t,
    (   SELECT @rownum := 0, @id1 := null, @id2 := null, @diff := null ) r
) x
WHERE x.rk = 1;

Answer 1

This is a working solution. Thanks @OMG Ponies for your help.

SELECT
 x.id1,
 x.id2,
 x.YEAR,
 x.MMDD,
 x.id3,
 x.id3_YEAR,
 x.id3_MMDD
FROM
( SELECT
  t.*,
  @rownum := CASE 
   WHEN @id1 = t.id1 AND @id2 = t.id2 THEN @rownum + 1
   ELSE 1
   END AS rk,
  @id1 := t.id1,
  @id2 := t.id2
 FROM
 ( SELECT
   t.*,
    ABS(DATEDIFF(CONCAT(CAST(t.id3_YEAR AS CHAR(4)),'-', LEFT(t.id3_MMDD,2),'-',RIGHT(t.id3_MMDD,2)),
    CONCAT(CAST(t.YEAR AS CHAR(4)),'-', LEFT(t.MMDD,2),'-',RIGHT(t.MMDD,2)))) AS diff
  FROM temp3 t
  ORDER BY t.id1, t.id2, diff, RAND()
 ) t,
 ( SELECT @rownum := 0, @id1 := null, @id2 := null ) r
) x
WHERE x.rk = 1;