assign same rank to row with same property in mysql -

I am using the following question to remove random lines from people with minimum date difference within a sub-group ( Defined ID 1, by ID 2 pair).

This is not working, because rank = 1 in sequential steps in rows according to date difference, only first observation (like the second one should also get the same date difference and one rank = 1 ).

Either way I need to change the WHEN clause in some way ...

  when @ id1 = t.id1 and @ id2 = t.id2 and @ Diff! = Diff THEN @rownum: = @rownum + 1 ELSE @rownum: = 1  

But when I do this I can not find the difference field ...

  drop table if existing temp4; Select table temp4 from x.id1, ​​x.id2, x.YEAR, x.mmdd, x.id3, x.id3_year, x.id3_MMDD, x.rk (SELECT t. *, ABS (DATEDIFF (CONCAT ( CAST (Charge (4) as TAD 3_Eyear, '-', LEFT (TD3mmdd, 2), '-', right (tid 3mmdd, 2)), conct (cst (tiiear AS Cheer (4)), '-', LEFT (TMMDD, 2), '-', right (TMMDD, 2))) FIF, agencies when @ id1 = TID1 and @ id2 = TID2 THEN @ Rownum: @ @Rownum + 1 ELSE @rownum: RK as = 1 END, @ id1: = t.id1, ​​@ id2: = t.id2 to temp3 t zone (SELECT @rownum: = 0, @ Id1: = 0, @ id2: = 0) by order TID1, TAD2, FIF, RAD ()) X;  

I am pasting SQL dump

  drop table if the current is 'Samule'; No 'zero', 'year' year (4) no, zero, `mmdd 'four (4) no id 3` `` id' Four (7) no tap, `id 3_aarar` year (4) no tap,` id 3_MMDD four (4) no tap) engine = mice default charset = latin1; Insert `Temp3` values ​​('1', '2', 1992, '0107', '55', 1991, '0528'); INSERT in `Temp3` values ​​('1', '2', 1992; '0107', '57', 1991; '0701'); Insert `Temp` values ​​('1', '3', 1992, '0107', '88', 2000, '0101'); INSERT in `Temp3` values ​​('1', '3', 1992; '0107', '44', 2000, '0101');  

This version is actually really close to what I need

  drop table if existing temp4; Select temp4 as select x.id1, ​​x.id2, x.YEAR, x.mmdd, x.id3, x.id3_yyar, x.id3_MMDD (SELECT t. *, @rownum: = case when @ Id1 = t Id1 and @ id2 = t.id2 and @diff! = Diff THEN @Rownum + 1 as ELSE 1st END RK, @ id1: = t.id1, ​​@ id2: = t.id2, @diff : = T.diff FROM (Select T. *, ABS (DATEDIFF (CONCAT (CAST (TAD3_AEAR AR Chart (4)), '-', LEFT (TD3mmdd, 2), '-', right (TD3 MMDD, 2)), CONCAT (CAST (TAIR AR Chair (4)), '-', Left (TMMDD, 2), '-', Right (TMMDD, 2)) .ID1, t.id2, diff, RAND ()) T, (SELECT @rownum: = 0, @ id1: = null, @ id2: = null, @diff: = null) r) x WHER E x.rk = 1;  

This is a workable solution for helping OMG Thanks to Tony's.

SELECT x.id1, ​​x.id2, x.YEAR, x.MMDD, x.id3, x.id3_YEAR, x.id3_MMDD (SELECT t. * , @rionayam: = case when id = 1 = TID1 and @ id2 = TID2, then @RoNum + 1 BSE1 and RK, AD1: = TID1, AD2: = TID2 Choose FDA, T *, ABS (DATEDIFF (CONCAT (CAD) (TAD 3AEAAR AR Chart (4)), '-', LEFT (TD3mmdd, 2), '-', right (TD3mmdd, 2)), conct (cst Tier ER Chair (4)), '-', Left (TMMDD, 2), '-', Right (TMMDD, 2)) .ID1, t.id2, diff, RAND ()) T, (SELECT @rownum : = 0, @ id1: = null, @ id2: = null) r x x WHERE x.rk = 1;