CODEWITHSUNDEEP
mysqlsqlsqlite
Adithya Gokhale
Adithya Gokhale

Reputation: 13

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 :)

Upvotes: 0

Views: 34

Answers (1)

Wasiq Muhammad
Wasiq Muhammad

Reputation: 3118

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

Upvotes: 1

Related Questions