Question 113522 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL Question on New entries after a given date

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
Tags:

4

Answers


  1. 0

    Using aggregation we can try:

    SELECT Identifier, MAX(Date) AS Date
FROM yourTable
GROUP BY Identifier
HAVING MIN(Date) = MAX(Date);
    Login or Signup to reply.
  2. 0

    Using QUALIFY and COUNT:

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

    Output:

    enter image description here

    Login or Signup to reply.
  3. 0

    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)
    Login or Signup to reply.
  4. 0 
    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search