Adithya Gokhale Adithya Gokhale - 4 months ago 9
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.

Example:

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
app1
,
app5
,
app7
.

host2 runs
app2
,
app6
,
app8
.

host3 runs
app4
only.

so host3 is least loaded. When I run the query, the output should be "host3". Rows of
type=0
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

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
Comments