CODEWITHSUNDEEP
sqlsql-servert-sql
Jonathan Ayling
Jonathan Ayling

Reputation: 25

MS SQL: How to select the most recent record from a lookup table for each foreign key pair

I have a lookup table which consists of two foreign keys which form a unique pair. These unique pairs are repeated a number of times within the table each time a transaction is made, with a new date/time being recorded each time.

I need to find a way to return just the "most recent" record for each foreign key pairing, and be able to apply additional filtering to it with a WHERE statement.

Here's an example of the table:

PK     PartPK  LocationPK  TransactionDate          Bin
16473  1       1           2018-09-03 15:24:57.100  NULL
16472  1       1           2018-09-03 13:24:27.250  NULL
16471  1       1           2018-09-03 13:07:11.777  NULL
16470  1       1           2018-09-03 11:19:57.557  NULL
16469  1       2           2018-09-03 09:32:27.050  NULL
16468  2       1           2018-09-03 07:28:16.250  NULL
16467  2       1           2018-09-03 00:09:30.383  NULL
16466  2       1           2018-08-31 14:21:24.803  NULL
16465  2       1           2018-08-31 13:29:52.253  NULL
16463  3       1           2018-08-31 13:13:47.977  NULL

The correct query should be able to give the following result:

PK     PartPK  LocationPK  TransactionDate          Bin
16473  1       1           2018-09-03 15:24:57.100  NULL
16469  1       2           2018-09-03 09:32:27.050  NULL
16468  2       1           2018-09-03 07:28:16.250  NULL
16463  3       1           2018-08-31 13:13:47.977  NULL

For each distinct PartPK and LocationPK pairing, I just want the row with the greatest TransactionDate value. I would also like to be able to build on this by adding a "WHERE Bin is null" to the query, to further limit the results.

How do I achieve this?

Upvotes: 1

Views: 58

Answers (3)

Shushil Bohara
Shushil Bohara

Reputation: 5656

First we should simply fetch the most recent PK of each PartPK and LocationPK then we can use it as subquery to return complete record of that PK as given below:

CREATE TABLE #test(PK INT, PartPK INT, LocationPK INT, TransactionDate DATETIME, Bin INT)
INSERT INTO #test VALUES
(16473,1,1,'2018-09-03 15:24:57.100',NULL),
(16472,1,1,'2018-09-03 13:24:27.250',NULL),
(16471,1,1,'2018-09-03 13:07:11.777',NULL),
(16470,1,1,'2018-09-03 11:19:57.557',NULL),
(16469,1,2,'2018-09-03 09:32:27.050',NULL),
(16468,2,1,'2018-09-03 07:28:16.250',NULL),
(16467,2,1,'2018-09-03 00:09:30.383',NULL),
(16466,2,1,'2018-08-31 14:21:24.803',NULL),
(16465,2,1,'2018-08-31 13:29:52.253',NULL),
(16463,3,1,'2018-08-31 13:13:47.977',NULL)

SELECT t.* 
FROM #test t
INNER JOIN (
            SELECT MAX(PK) AS PK --We can use `TransactionDate` as well
            FROM #test
            GROUP BY PartPK, LocationPK) t1 ON t.PK = t1.PK

OUTPUT:

PK      PartPK  LocationPK  TransactionDate         Bin
16473   1       1           2018-09-03 15:24:57.100 NULL
16469   1       2           2018-09-03 09:32:27.050 NULL
16468   2       1           2018-09-03 07:28:16.250 NULL
16463   3       1           2018-08-31 13:13:47.977 NULL

Upvotes: 1

Cato
Cato

Reputation: 3701

SELECT Q2.* FROM (SELECT DISTINCT t.PartPK, t.LocationPK FROM Yourtab t) Q1
                CROSS APPLY 
                    (SELECT TOP 1 * FROM Yourtab t2 
                            WHERE t2.PartPK = Q1.PartPK AND t2.LocationPK = Q1.LocationPK 
                                ORDER BY TransactionDate DESC) Q2

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can use row_number() function :

select t.*
from (select t.*, row_number() over (partition by PartPK, LocationPK order by PK desc) as seq -- you can also order by TransactionDate
      from table t
     ) t
where seq = 1;

Upvotes: 3

Related Questions