skip to Main Content

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


  1. 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;
    
    Login or Signup to reply.
  2. 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:

    profile_id last_profile_date_time
    102 2023-06-05 08:35:19.5393

    PostgreSQL fiddle Oracle fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search