Adithya Gokhale Adithya Gokhale - 1 year ago 38
MySQL Question

SELECT query to select based on number of rows

I have a requirement where I want to run a SELECT query with a where clause and group by. It returns few rows out of which I want to pick the rows which are least in number.


table t1:
host_name application type
host1 app1 0
host2 app2 0
host3 app4 0
host1 app5 0
host2 app6 0
host1 app7 1
host2 app8 1

In the above table i want to get the host which is least loaded.

host1 runs

host2 runs

host3 runs

so host3 is least loaded. When I run the query, the output should be "host3". Rows of
only must be chosen.

I have solution. But is it optimized ? is there a best optimized way to achieve this ?

SELECT host_name from (SELECT host_name, min(count) FROM (SELECT host_name, COUNT(*) as count FROM t1 where type=0 group by host_name) as Dup ORDER BY host_name);

Thanks in advance :)

Answer Source

Try this, it will return least host by number of apps:

select `host_name` 
from t1
where type = 0
group by `host_name`
order by count(`host_name`) ASC
limit 0,1