SQL Question on New entries after a given date

RDJ 0
Viewed 34

Question: SQL query to best get the desired result below.

Input:

Identifier Date
1234 2022-01-01
1234 2022-08-11
7890 2022-08-11
4444 2022-08-13

Output:

Identifier Date
7890 2022-08-11
4444 2022-08-13

Note: Want only new entries that was not in the past. Tried doing a self left join with the same table with a.Identifier = b.Identifier and a.date < b.date but it gives me 1234 entry that I do not need.

My Query (Yielding extra entry of 1234)

select * from
Table a left join Table b
on a.Identifier = b.Identifier
and a.Date < b.Date
where b.Identifier is null
snowflake-cloud-data-platform mysql sql
4 Answers

Using aggregation we can try:

SELECT Identifier, MAX(Date) AS Date
FROM yourTable
GROUP BY Identifier
HAVING MIN(Date) = MAX(Date);
Tim Biegeleisen0

Using QUALIFY and COUNT:

SELECT *
FROM table_name
-- WHERE ...
QUALIFY COUNT(*) OVER(PARTITION BY Identifier) = 1;

Output:

enter image description here

Lukasz Szozda0

The way you phrased the question made your problem sound more complicated than it actually is. If you just need to filter for identifiers that don't repeat in the table, all you need to do is get the counts and limit it to 1. Lukasz's answers achieves the same using qualify

select *
from table_name
where identifer in (select identifier from table_name group by identifier having count(*)=1)
Phil Coulson0

select * from table_name where identifier >= '4444' if you using double table maybe you can use select * from table_name_a inner join table_name_b on table_name_a.id = table_name_b.id where identifier >= '4444' i hope i can help you

Rezky0
Related