CODEWITHSUNDEEP
sql
Fed_driv
Fed_driv

Reputation: 37

how to find a date earlier than the oldest one in another column; in SQL

I have two dates columns date1 and date2 and I would like to take only ids that have date1 earlier than the oldest date in date2 for each id, as date1<= min(date2). For instance:

ID :::    Date1 :::      Date2
ID_1 :::  2010-01-01 :::  2010-04-03 
ID_1 :::  2010-03-02 :::  2010-04-03 
ID_1 ::: 2007-05-04 ::: 2008-04-05  <--- I would this

I get an aggregation problem. I should add date1 to the clause group by, but I get result that I don't want. How can I fix this problem? In the final code, that I have reported below, I would the ID and the repeated measures done in Date1, that was done before the oldest Date2 for that ID. Thanks in advance

SELECT  ID, count(Date1)
FROM myTable
where (field like '%hi%')
group by ID, Date1
having (count(Date1) <= 500
    and Date1 <= min(Date2))
order by ID

Upvotes: 0

Views: 863

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

If you want the original rows, you can use window functions:

select t.*
from (select t.*, min(date2) over (partition by id) as min_date2
      from mytable t
     ) t
where date1 < min_date2;

If you want only ids, then you are saying the min(date1) < min(date2), so aggregation is sufficient:

select id
from mytable
group by id
having min(date1) < min(date2);

And, if you want to, you can use a correlated subquery instead:

select t.*
from mytable t
where t.date1 < (select min(t2.date) from mytable t2 where t2.id = t.id);

Upvotes: 1

Related Questions