CODEWITHSUNDEEP
mysqlsqlselect
lwb
lwb

Reputation: 389

Can not select distinct on my table

How can I select only the distinct callers from my database where some calls are not answered? For example: In this sample table i would like to select only the numbers 6666666 (at 2014-09-08 10:48:52) and 5555555 (at 2014-09-08 11:52:10) because they where not answered by none extension. I Tried 

SELECT DISTINCT * from cdr WHERE disposition !='ANSWERED';

But this returns everything who not have the string 'ANSWERED' in disposition. Can anyone help me? Thanks!! Table Sample:
https://i.sstatic.net/CKZsh.png

Upvotes: 0

Views: 81

Answers (2)

FuzzyTree
FuzzyTree

Reputation: 32392

select date, src
from cdr
group by date, src
having sum(disposition = 'ANSWERED') = 0

in response to comment, if you need the other columns I suggest joining to the result above. Otherwise mysql will return a random value from each group for the columns that are not part of the group by. i.e.

select cdr.* from cdr join (
    select date, src
    from cdr
    group by date, src
    having sum(disposition = 'ANSWERED') = 0
) t1 on t1.date = cdr.date and t1.src = cdr.src

Upvotes: 1

Linger
Linger

Reputation: 15048

SELECT * 
from cdr t1
LEFT JOIN cdr t2 ON t1.SRC = t2.SRC  
      AND t1.Date = t2.Date
      AND t1.Disposition <> t2.Disposition
WHERE t1.disposition = 'NO ANSWER'
GROUP BY t1.Date, t1.SRC
HAVING COUNT(*) = 1;

Upvotes: 3

Related Questions