Here below is a table that stores a one-to-many relationship where one user might be associated with one or more profiles:
+----+---------+------------+------------------------+
| ID | USER_ID | PROFILE_ID | LAST_PROFILE_DATE_TIME |
+----+---------+------------+------------------------+
| 1 | 100 | 101 | 04.06.23 08:35:19.5393 |
| 2 | 100 | 102 | 05.06.23 08:35:19.5393 |
+----+---------+------------+------------------------+
How do I get the latest profile for user 100
? Here’s the expected result:
+------------+
| PROFILE_ID |
+------------+
| 102 |
+------------+
I need a single value to be used to update a field in another table.
This is my attempt… but the problem is that I get two rows because the GROUP BY
clause requires all the fields but MAX
:
SELECT up.PROFILE_ID
FROM
(SELECT USER_ID, PROFILE_ID, MAX(LAST_PROFILE_DATE_TIME)
FROM USER_PROFILE
GROUP BY USER_ID, PROFILE_ID) up
WHERE up.USER_ID = 100;
I need to run the query on both Oracle and PostgreSQL. Any help would be really appreciated.
2
Answers
You can do it as follows :
First get the latest LAST_PROFILE_DATE_TIME per USER_ID :
Then join this dataset to your table to obtain PROFILE_ID:
You can use the
ROW_NUMBER
analytic function:or both databases support the
FETCH FIRST ROW ONLY
syntax:Which, for the sample data:
Both output:
PostgreSQL fiddle Oracle fiddle