MySQL Forums
Forum List  »  General

SQL sub-select
Posted by: James Caravan
Date: June 01, 2018 09:21AM

Hi all
This might be a simple sub-select statement but I must be missing something.
I'm trying to get the max(CompletedDate) in a group UserID's and Apps, but want to include the unique autogenerated "ID" associated with the max completed date.
The max ID may or may not have the max CompletedDate in the group

UserID App Trans ID CompletedDate
11 DEXTER Delete 33864 2/19/2003
11 DEXTER Add 37513 11/19/2003
11 DSSMP13 Add 64588 4/11/2007
11 DSSMP13 Delete 134721 10/11/2017
11 DSSMP5 Add 17145 6/8/2000
11 DSSMP5 Delete 24687 4/10/2007
11 DSSMP5 Add 64589 9/11/2001
12 MSEXCH Add 10471 12/10/1998
12 MSEXCH Delete 12067 4/30/1999
12 OV/VM Add 1982 1/1/1997
12 OV/VM Change 11381 4/30/1999
12 OV/VM Delete 12068 2/25/1999

this would be the result:
11 DEXTER Add 37513 11/19/2003
11 DSSMP13 Delete 134721 10/11/2017
11 DSSMP5 Add 64589 4/10/2007
12 MSEXCH Delete 12067 4/30/1999
12 OV/VM Delete 12068 2/25/1999

I can get the max(ID) in the group of UserID's since I don't need another associated value:

SELECT UserID, App, Tran, ID, CompletedDate
FROM Access
WHERE Access.ID IN
( SELECT MAX( Access.ID )
FROM Access
GROUP BY UserID, App )
ORDER BY UserID, App, Access.ID

I'm using the built in Query builder from the TQuery component.

Any help would be appreciated!

thanks

Options: ReplyQuote


Subject
Written By
Posted
SQL sub-select
June 01, 2018 09:21AM
June 01, 2018 02:01PM
June 01, 2018 04:51PM
June 02, 2018 06:03AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.