DASOFT DASOFT - 3 years ago 158
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

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