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

Question

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)

Answer 1

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.