Search code examples
mysqlsqlsqlyog

Max(date) with inner join and where clause

I have 2 tables, user and order.
Both name and group in user table form a unique combination, so note that here we have total of 5 unique combination in the entries.
For each unique combination, i would like to find their latest respective status, only concern about active and inactive.
Then, I'll ignore the active status, and output query with the inactive status.
(but currently, im only query for all active/inactive results because i dont want to make things more difficult)

sample Here are two closest sql statement ive written (wrong)
this returns all unique combination with status:active/inactive 

SELECT order.id, order.status, order.someA, order.someB, order.someC, order.date, user.name, user.group, user.site FROM order INNER JOIN user ON user.id = order.id WHERE (status = 'P' OR status = 'F');

this only give first entry of active/inactive for each unique combination (should be last/latest entry)

SELECT MAX(order.date), order.date, order.id, order.status, order.someA, order.someB, order.someC, user.name, user.group, user.site FROM order INNER JOIN user ON user.id = order.id WHERE site = 'US' AND (status = 'P' OR status = 'F') GROUP BY name, group ORDER BY date;

id name group site
K01 John Texas US
K02 Niall Ohio US
K03 sally Ohio US
K04 Simon Ohio US
K05 James Texas US
K06 James Texas US
K07 John Texas US

id status someA someB someC date (timestamp)
K01 active I just randomly 21-09-2018 19:22:33 PM
K02 inactive put some words 22-09-2018 16:22:33 PM
K03 active here for this 22-09-2018 19:22:33 PM
K04 unknown dummy example output 22-09-2018 19:22:33 PM
K05 inactive X S V 23-09-2018 19:22:33 PM
K06 unknown hope i explain 23-09-2018 19:22:33 PM
K07 unknown my question well 24-09-2018 19:22:33 PM

The data above is tab-delimited, let me know if it's not able to import into excel.
welcome to edit my sentences, I might not have the perfect sentence to describe my question.

Solution

  • If you want the most recent order, you can do:

    select . . .   -- unclear what columns you really want
from users u join
     orders o
     on u.id = o.id
where (u.name, u.group, o.date) in
       (select u2.name, u2.group, max(o2.date)
        from users u2 join
             orders o2
             on u2.id = o2.id
        group by u2.name, u2.group
       ) and
       o.status <> 'active';