How to return a unique record-CodePudding

I have an SQL scenario where I want to return records 2 weeks or older from a table, but only if there aren't other records more recent with the same join. Here are the two tables:

PRIMARY TABLE:

ID | SECONDARYID | CREATION_DATE | DESCRIPTION
---------------------------------------------------

SECONDARY TABLE:

ID | NAME | PHONE_NUMBER ....
---------------------------------------------------

The primary table has the following records:

ID | SECONDARYID  | CREATION_DATE | DESCRIPTION
---------------------------------------------------
3  | 305          | 2022-01-14    | some other description
2  | 300          | 2022-01-10    | some 2nd description
1  | 300          | 2022-01-01    | some description

So far I have the following query.

SELECT DISTINCT *
FROM PRIMARY
JOIN SECONDARY ON PRIMARY.SECONDARYID = SECONDARY.ID
WHERE PRIMARY.CREATIONDATE BETWEEN (NOW() - INTERVAL 14 DAY) AND NOW()

It returns the record from the primary table with an ID of 1. However, I want to update this query so that it doesn't return that record since there is a newer entry with the same secondaryid that isn't older than 2 weeks. Please assist.

CodePudding user response：

You can inner join to a table with max creation_date per secondaryid to ensure you have only latest creation_date

SELECT DISTINCT *
FROM PRIMARY
JOIN SECONDARY ON PRIMARY.SECONDARYID = SECONDARY.ID
inner join (
  select 
  secondaryid
  , max(creationdate) maxdate 
  from PRIMARY 
  group by secondaryid) mx 
on PRIMARY.seconardyid = mx.secondaryid and mx.maxdate = PRIMARY.creationdate 
WHERE PRIMARY.CREATIONDATE BETWEEN (NOW() - INTERVAL 14 DAY) AND NOW()