Max(date) with inner join and where clause

Question

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)

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.

See: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query), and note that both `order` and `group` are reserved words. — Strawberry, Sep 24 '18 at 12:50
@Strawberry, thank you for telling me those two were reserved words, im just using it for this dummy. I should be careful next time — Santa, Sep 24 '18 at 12:54

score 0 · Accepted Answer · answered Sep 24 '18 at 12:50

0

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';

answered Sep 24 '18 at 12:50

Gordon Linoff

1,242,037
58
646
786

thanks for your response. I'll try it when i got the access to sql which i dont have now, hmm, the columns i want to return is in desired output aka the third table in excel. – Santa Sep 24 '18 at 12:58
hi @Gordon, strangely it return nothing with no errors. I am sure there should be matching entries . :( – Santa Sep 25 '18 at 02:51
@Santa . . If it returns no rows and no error, then there are no matches. The issue is probably the filtering on "active". Try without that condition. – Gordon Linoff Sep 25 '18 at 12:37

Max(date) with inner join and where clause

1 Answers1