TID INT,

SID VARCHAR(30),

REVISION INT,

IMPORT_FLAG INT,

AMOUNT INT,

ITEM VARCHAR(30),

FLAG_UPDATE_TIME DATETIME

);

INSERT INTO item VALUES

(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');

SELECT * FROM item

TID INT,

SID VARCHAR(30),

REVISION INT,

UNIQUE_CODE VARCHAR(30)

);

INSERT INTO UNIQUE_ITEM_CODE VALUES

(23456, '0', 0, 'ASF456B'),

(65743, '0', 0, 'FDGHJY6'),

(76543, 'AB', 1, 'ET567BG'),

(86364, 'F', 2, 'MHFB78'),

(86364, 'N', 0, '567HFV8');

SELECT * FROM UNIQUE_ITEM_CODE

UNIQUE_CODE VARCHAR(30),

PRIORITY VARCHAR(30),

CLASS VARCHAR(30),

TYPE VARCHAR(30),

STATUS VARCHAR(30),

LAST_UPDATE_TIME DATETIME

);

INSERT INTO UNIQUE_HISTORY VALUES

('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-20-2023 07: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');

SELECT * FROM UNIQUE_HISTORY

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)