CODEWITHSUNDEEP
sqlsql-server
Dmitry.R
Dmitry.R

Reputation: 87

SQL: Get dates with two specific events

Table: 
    Logs
Fields:    
    LogDate datetime,
    ErrId int

ErrId 9 and 5 sometimes occur on same date. How can I find those dates? Of course the task can be about any pair of ErrIds not just 9 and 5.

I ended up with following sql statement:

select  distinct l_1.LogDate
from logs l_1
where exists (select * from logs l_2 where l_1.LogDate = l_2.LogDate and l_2.ErrId = 9)
  and exists (select * from logs l_3 where l_1.LogDate = l_3.LogDate and l_3.ErrId = 5)

The question: is there better solution for the task?

Upvotes: 1

Views: 51

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

Perhaps the easiest method is simply group by with having:

select l.LogDate
from logs l
group by l.log_date
having sum(case when l.ErrId = 9 then 1 else 0 end) > 0 and
       sum(case when l.ErrId = 5 then 1 else 0 end) > 0;

If you are only looking for a fixed set of values, you can also write this as:

select l.LogDate
from logs l
where l.ErrId in (5, 9)
group by l.log_date
having count(distinct l.ErrId) = 2;

Both of these should be faster than doing a group by/distinct along with a join.

Upvotes: 1

trincot
trincot

Reputation: 350272

You could use a self-join like this:

select     distinct l_1.LogDate
from       logs l_1
inner join logs l_2 on l_1.LogDate = l_2.LogDate
where      l_1.ErrId = 9
and        l_2.ErrId = 5

Note that generally it is better to use group by instead of distinct. Give it a try:

select     l_1.LogDate
from       logs l_1
inner join logs l_2 on l_1.LogDate = l_2.LogDate
where      l_1.ErrId = 9
and        l_2.ErrId = 5
group by   l_1.LogDate

Upvotes: 1

Related Questions