SQL Query to get unique list

Question

I have a table named 'resources'

Machine     Host        Enabled
mach1       host1       TRUE
mach2       host1       FALSE
mach3       host1       FALSE
mach4       host2       TRUE
mach5       host2       TRUE

I want to get the list of hosts where Enabled is True for all the resources/machines associated with them.

I tried the sql query-

select distinct Host from resources where Enabled = TRUE;

But that query gives me back both host1 and host2 as the answer whereas I am expecting the answer to be just host2. Can anyone help me with a sql query which can achieve this ?

score 2 · Accepted Answer · answered Jan 26 '16 at 21:43

2

Try this:

SELECT Host
FROM resources
GROUP BY Host        
HAVING COUNT(*) = COUNT(CASE WHEN Enabled = True THEN 1 END)

or:

SELECT DISTINCT Host 
FROM resources AS r1
WHERE Enabled = TRUE AND 
      NOT EXISTS (SELECT 1 
                  FROM resources AS r2
                  WHERE r1.Host = r2.Host AND r2.enabled = FALSE)

answered Jan 26 '16 at 21:43

Giorgos Betsos

71,379
9
63
98

Thanks. Both the queries work. What is the difference in between using Group By and Distinct ? – sid Jan 26 '16 at 22:18
1

@sid Have a look [here](http://stackoverflow.com/questions/581521/whats-faster-select-distinct-or-group-by-in-mysql) for an answer to your question. Also, you may accept any of the available answers if they help you solve your problem. – Giorgos Betsos Jan 27 '16 at 08:53
All the answers posted here worked. But the first solution posted here is most useful for me, hence accepted this answer. – sid Jan 29 '16 at 21:08

score 2 · Answer 2 · answered Jan 26 '16 at 21:58

2

TRY THIS ONE AND LET ME KNOW.

SELECT DISTINCT(Host)
FROM Resources
WHERE Enabled = TRUE AND Host NOT IN (Select Host FROM Resources WHERE Enabled = FALSE)

answered Jan 26 '16 at 21:58

Mytroy2050

179
1
2
17

This works too. However, I will add a limit 1 in the end of the second select statement. It will probably make the query faster. – sid Jan 26 '16 at 22:25
Yes, That will make query faster. – Mytroy2050 Jan 27 '16 at 14:01

score 1 · Answer 3 · answered Jan 26 '16 at 21:45

1

Try

select distinct Host from resources res1 where not exist (
   select 1 from resources res2 WHERE res1.host = res2.host AND Enabled = FALSE limit 1
);

answered Jan 26 '16 at 21:45

Alon Eitan

11,997
8
49
58

score 0 · Answer 4 · answered Jan 27 '16 at 10:03

0

This works too

select host from resources e
having count(e.host)=(select count(enebled) from resources 
                    where enebled='TRUE' and host = e.host)
group by host;

answered Jan 27 '16 at 10:03

Narasimha Maiya

1,009
4
12
35

SQL Query to get unique list

4 Answers4