CODEWITHSUNDEEP
mysqlsqldatabase
Dipak
Dipak

Reputation: 692

Getting Unexpected result in using min(key)

i am having two tables ss_test and ss_ceastore_config ss_test has field called store_id which maps to ss_ceastore_config id. my ss_test contains servers entry which tells that which store it is using so i am trying to find which store is used min times and its id. i have below query written.

 select id,
        min(server_counts) as server_counts,
        isalive 
 from (select ss_ceastore_config.id ,
              count(server_id)as server_counts, 
               ss_ceastore_config.isalive 
       from ss_test 
       right join ss_ceastore_config 
       on ss_test.store_id = ss_ceastore_config.id 
       group by store_id 
       order by id) join_1

my inner query gives me proper result as below 

id Ascending    server_counts   isalive
1                 5               1
2                 0               1

so i want to select below record from inner query output using min function

id Ascending    server_counts   isalive
2                 0               1

but it gives unexpected result with my outer query as below

id  server_counts   isalive
1         0            1

why this so? why it is giving id 1 for server_counts 0? how to fix this query?

Upvotes: 1

Views: 48

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521028

select id,
    server_counts,
    isalive 
from (select ss_ceastore_config.id ,
          count(server_id)as server_counts, 
           ss_ceastore_config.isalive 
   from ss_test 
   right join ss_ceastore_config 
   on ss_test.store_id = ss_ceastore_config.id 
   group by store_id 
   order by id) join_1
order by server_counts asc
limit 1;

Your original query was not working because you were doing an aggregate SELECT using the MIN() function along with non-aggregate columns such as id and isalive. I believe that MySQL makes no guarantee about which id value it will return along with the minimum for that column.

My strategy is to return all rows in ascending order by server_counts, and then to SELECT only the first row (which is the minimum).

Upvotes: 2

Related Questions