MySQL Question
SQL: select exact max for repeating value that based on other repeating value
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 |
I need to get MAX(date) for each 'code', and get 'userid' for this MAX(date) and 'code', with group by 'code'.
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
But, MAX(date) value should be based on userid.
If code has user 1 and 2 and 3 then max date should be based on user 3 codes.
If code has user 2 and 3 then max date should be based on user 3 codes.
If code has user 1 and 2 then max date should be based on user 2 codes.
If code has user 3 then max date should be based on user 3 codes.
If code has user 2 then max date should be based on user 2 codes.
If code has user 1 then max date should be based on user 1 codes.
Response shuld contain: code, userid, and MAX(date) based on 'userid' value with gpoup by 'code' (one row for each code)
How to add this select?
Thanks for any help)
SQL Fiddle
Recommended for you: Get network issues from WhatsUp Gold. Not end users.
Answer Source
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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download