Table with repeating data in each column
| userid | code | date |
|3 | YYYYYY | 2017-09-20 |
|3 | YYYYYY | 2017-09-21 |
|3 | YYYYYY | 2017-09-22 |
|1 | XXXXXX | 2017-09-22 |
|1 | XXXXXX | 2017-09-23 |
|3 | XXXXXX | 2017-09-23 |
|3 | XXXXXX | 2017-09-23 |
|2 | ZZZZZZ | 2017-09-23 |
|3 | ZZZZZZ | 2017-09-23 |
|1 | ZZZZZZ | 2017-09-24 |
SELECT
t1.userid,
t1.code,
t1.date
FROM codes AS t1
INNER JOIN (
SELECT
userid,
code,
MAX(date) as maxdate
FROM codes
GROUP BY code
) AS t2
ON (t1.code = t2.code AND t1.date = t2.maxdate)
ORDER BY date
SELECT
t1.userid,
t1.code,
t1.date
-- SOME OTHER ROWS FROM TABLE
FROM
codes AS t1
-- JOIN TABLE WITH SEARДH ROWS
INNER JOIN (
-- BEGIN SELECT MAX LOGIC
SELECT
code,
CASE WHEN max3date IS NOT NULL THEN max3date WHEN max2date IS NOT NULL THEN max2date ELSE max1date END AS maxdate
FROM
(
-- BEGIN SELECT MAX FOR EACH GROUP
SELECT
all1.code,
u3.max3date,
u2.max2date,
u1.max1date
FROM
codes AS all1
LEFT JOIN (
SELECT
code,
MAX(date) AS max3date
FROM
codes
WHERE userid LIKE '3%'
GROUP BY
code
) AS u3 ON (all1.code = u3.code)
LEFT JOIN (
SELECT
code,
MAX(date) AS max2date
FROM
codes
WHERE userid LIKE '2%'
GROUP BY
code
) AS u2 ON (all1.code = u2.code)
LEFT JOIN (
SELECT
code,
MAX(date) AS max1date
FROM
codes
WHERE userid LIKE '1%'
GROUP BY
code
) AS u1 ON (all1.code = u1.code)
GROUP BY
code
) t3
) AS t2 ON (
t1.code = t2.code
AND t1.date = t2.maxdate
)
ORDER BY
t1.date
It works fine for me on 24M rows. Date (time) should be unique. SQL Fiddle