SQL: How to Retrieve the Most Recent Association in a One-To-Many Relationship

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.

You can do it as follows :

First get the latest LAST_PROFILE_DATE_TIME per USER_ID :

  select USER_ID, max(LAST_PROFILE_DATE_TIME) as MAX_LAST_PROFILE_DATE_TIME
  from USER_PROFILE
  where USER_ID = 100
  group by USER_ID

Then join this dataset to your table to obtain PROFILE_ID:

select t.PROFILE_ID
from USER_PROFILE t
inner join (
  select USER_ID, max(LAST_PROFILE_DATE_TIME) as MAX_LAST_PROFILE_DATE_TIME
  from USER_PROFILE
  where USER_ID = 100
  group by USER_ID
) as s on s.USER_ID = t.USER_ID and s.MAX_LAST_PROFILE_DATE_TIME = t.LAST_PROFILE_DATE_TIME;

You can use the ROW_NUMBER analytic function:

SELECT profile_id,
       last_profile_date_time
FROM   (
  SELECT profile_id,
         last_profile_date_time,
         ROW_NUMBER()
           OVER (ORDER BY last_profile_date_time DESC) AS rn
  FROM   table_name
  WHERE  user_id = 100
  ORDER BY last_profile_date_time DESC
) q
WHERE  RN = 1;

or both databases support the FETCH FIRST ROW ONLY syntax:

SELECT profile_id,
       last_profile_date_time
FROM   table_name
WHERE  user_id = 100
ORDER BY last_profile_date_time DESC
FETCH FIRST ROW ONLY;

Which, for the sample data:

CREATE TABLE table_name (
  ID         INT,
  USER_ID    INT,
  PROFILE_ID INT,
  LAST_PROFILE_DATE_TIME TIMESTAMP
);

INSERT INTO table_name (id, user_id, profile_id, last_profile_date_time)
  VALUES (1, 100, 101, TIMESTAMP '2023-06-04 08:35:19.5393' );
INSERT INTO table_name (id, user_id, profile_id, last_profile_date_time)
  VALUES (2, 100, 102, TIMESTAMP '2023-06-05 08:35:19.5393' );

Both output:

PostgreSQL fiddle Oracle fiddle