SQL: Selecting record where values in one field are unique based off of most recent date

user_8206445 0
Viewed 66

I'm attempting to write an SQL statement to select records such that each record has a unique PartNo, and I want that record to be based off of the most recent ReceiveDate. I got an answer when I asked this question:

SELECT t.*
FROM Table as t
WHERE t.ReceiveDate = (SELECT MAX(t2.ReceiveDate)
                       FROM Table as t2
                       WHERE t2.PartNo = t.PartNo
                      );

However, this answer assumes that for each ReceiveDate, you would not have the same PartNo twice. In situations where there are multiple records with the same PartNo and ReceiveDate, it does not matter which is selected, but I only want one to be selected (PartNo must be unique)

Example:

PartNo | Vendor | Qty | ReceiveDate
 100   |  Bob   | 2   | 2020/07/30
 100   | Bob    | 3   | 2020/07/30

Should only return one of these records.

I'm using Microsoft Access which uses Jet SQL which is very similar to T-SQL.

ms-access tsql sql
2 Answers

Use NOT EXISTS:

select distinct t.*
from tablename as t
where not exists (
  select 1 from tablename
  where partno = t.partno
  and (
    receivedate > t.receivedate
    or (receivedate = t.receivedate and qty > t.qty)
    or (receivedate = t.receivedate and qty = t.qty and vendor > t.vendor)
  )
)
user_104988280

manually set up a standard Aggregate query (sigma icon in ribbon) where grouped on Part No and Date field is set to MAX...

run the query to check to see it returns the values you seek... then while in design view - - select SQL view and this will give you the sql statement...

user_84972520
Related