SELECT pdate , rn , CONCAT(pdate, LPAD(rn, 3, '0')) pk FROM (SELECT pdate , CASE WHEN @pdate = pdate THEN @rn := @rn + 1 ELSE @rn := 1 END AS rn , @pdate := pdate FROM (SELECT '20140701' pdate UNION ALL SELECT '20140701' UNION ALL SELECT '20140701' UNION ALL SELECT '20140702' UNION ALL SELECT '20140702' UNION ALL SELECT '20140703' ) a , (SELECT @rn := 0, @pdate := '') b ORDER BY pdate ) a ;