sqlsql-serverjoinsql-server-2012greatest-n-per-group

Find top 1 record from join table for unique id by timestamp


I have three tables, item, unique_item_code and unique_hist. item table has mapping relation with unique_item_code table and unique_item_code has mapping relation with unique_hist table. Every item has unique item code and that is available in unique_item_code table. unique_hist table contain the unique_code's other details and this table has multiple records for a unique code.

I have to get details from item table and unique_item_code table, but if there are more than 1 record available in unique_hist table for unique_code then take that latest one record which has last_updated_time less than flag_updated_time.

Table "Item":

TID SID REVISION IMPORT_FLAG AMOUNT ITEM FLAG_UPDATE_TIME
23456 0 0 0 657 BX 08-31-2023 10:20:10
65743 0 0 0 467 BX 08-30-2023 11:01:39
76543 AB 1 1 8753 DU 08-30-2023 09:01:39
86364 F 2 1 78 TY 09-03-2023 11:12:41
86364 N 0 1 5000 RT 08-22-2023 18:45:16

Table "UNIQUE_ITEM_CODE":

TID SID REVISION UNIQUE_CODE
23456 0 0 ASF456B
65743 0 0 FDGHJY6
76543 AB 1 ET567BG
86364 F 2 MHFB78
86364 N 0 567HFV8

Table "UNIQUE_HISTORY":

UNIQUE_CODE PRIORITY CLASS TYPE STATUS LAST_UPDATE_TIME
ASF456B HIGH TTH B CLOSED 08-31-2023 13:23:45
FDGHJY6 LOW CAD S OPEN 08-31-2023 09:32:25
ET567BG LOW AUS B OPEN 08-30-2023 10:00:11
MHFB78 LOW AUS B TERMINATED 09-03-2023 11:09:18
567HFV8 LOW EUR S OPEN 08-22-2023 18:28:56
ASF456B LOW CAD S CLOSED 08-15-2023 23:32:42
FDGHJY6 HIGH NXZ B HOLD 08-30-2023 03:14:25
FDGHJY6 HIGH NXZ B COMPLETE 08-30-2023 03:16:15
FDGHJY6 LOW TTH S CLOSED 08-30-2023 14:20:02

"Expected Output":

TID SID REVISION IMPORT_FLAG AMOUNT ITEM FLAG_UPDATE_TIME UNIQUE_CODE PRIORITY CLASS TYPE LAST_UPDATE_TIME
23456 0 0 0 657 BX 08-31-2023 10:20:10 ASF456B LOW CAD S 08-15-2023 23:32:42
65743 0 0 0 467 BX 08-30-2023 11:01:39 FDGHJY6 HIGH NXZ B 08-30-2023 03:16:15
76543 AB 1 1 8753 DU 08-30-2023 09:01:39 ET567BG LOW AUS B 08-20-2023 07:00:11
86364 F 2 1 78 TY 09-03-2023 11:12:41 MHFB78 LOW AUS B 09-03-2023 11:09:18
86364 N 0 1 5000 RT 08-22-2023 18:45:16 567HFV8 LOW EUR S 08-22-2023 18:28:56

I tried with this query:

select it.*, 
       hist.unique_code, 
       hist.priority, 
       hist.class, 
       hist.type, 
       hist.last_updated_time
from item it 
join unique_item_code uniq 
  on it.tid = uniq.tid 
 and it.sid = uniq.sid and it.revision = uniq.revision 
join unique_hist hist 
  on uniq.unique_code = hist.unique_code 
where it.flag_updated_time < hist.last_updated_time

And this one:

select it.*, 
       hist.unique_code, 
       hist.priority, 
       hist.class, 
       hist.type, 
       hist.last_updated_time 
from item it 
join unique_item_code uniq 
  on it.tid = uniq.tid 
 and it.sid = uniq.sid and it.revision = uniq.revision 
join (select top 1 
      from unique_hist hist 
      where it.flag_updated_time < last_updated_time 
        and uniq.unique_code = hist.unique_code)

Solution

  • You can unwrap your problem by filtering the records in your history table on your joined tables.

    In order to filter your records safely, to get the first record for each partition ordered by date, you could use TOP(1) WITH TIES when applied to a window function like ROW_NUMBER, that returns 1 for each partition, when two conditions on cascade are satisfied:

    • "flag_update_time" is lower than "last_update_time"
    • "last_update_time" is highest
    SELECT TOP(1) WITH TIES 
           i.TID, 
           i.SID, 
           c.REVISION, 
           i.IMPORT_FLAG,
           i.AMOUNT,
           i.ITEM,
           i.FLAG_UPDATE_TIME,
           c.UNIQUE_CODE,
           hist.PRIORITY,
           hist.CLASS,
           hist.TYPE,
           hist.LAST_UPDATE_TIME
    FROM       item i
    INNER JOIN UNIQUE_ITEM_CODE c
            ON i.TID = c.TID
           AND i.SID = c.SID
    INNER JOIN UNIQUE_HISTORY hist
            ON c.UNIQUE_CODE = hist.UNIQUE_CODE
    ORDER BY ROW_NUMBER() OVER(PARTITION BY hist.UNIQUE_CODE 
                               ORDER     BY CASE WHEN i.FLAG_UPDATE_TIME < hist.LAST_UPDATE_TIME THEN 1 ELSE 0 END, 
                                            hist.LAST_UPDATE_TIME DESC)  
    

    Check the demo here.