Join tables and sort by max date between the two tables and it's entries (there are two dates each)

41 Views Asked by At

I have two tables sgroup and sitem. both sgroup and sitem have a created and modified property (which are of type DATETIME and can be NULL).
There can be many sitem entries for each sgroup. I'd like to create a query that will return to me the properties of the group as well as the MAX of all of the sitem(s) or sgroup datetimes.
It's always possible modified is null (though this can be changed). It's possible there are 0 sitems. +1 if I can sort the results by date.
Here's an example.
sgroup: || id || a || b || c || created || modified ||
|| 1 || xyz || def || qwe || 2024-01-03 00:31:55 || NULL ||
|| 2 || wer || ert || rty || 2024-01-04 00:12:12 || 2024-01-07 00:32:51 ||

sitem: || id || gid || d || e || created || modified ||
|| 1 || 1 || hio || kso || 2024-01-06 00:31:55 || NULL ||
|| 1 || 2 || kop || noa || 2024-01-07 00:12:55 || NULL||

gid is a foreign key from sitem to sgroup. id is the primary key for both tables. As you notice Group 1 has 2 items, and Group 2 has no items. I'd like to perform a query and return something like the following:
|| id || a || b || c || latest ||
|| 2 || wer || ert || rty || 2024-01-07 00:32:51 ||
|| 1 || xyz || def || qwe || 2024-01-07 00:12:55 ||

As you can see the second group's latest datetime which is its modified property. In the first group the latest date comes from the creation of sitem 2. I'd like to receive only these 2 entries describing the groups.

0

There are 0 best solutions below