CODEWITHSUNDEEP
mysql
snaggs
snaggs

Reputation: 5713

How to use "group by" and max value into inner join queries?

I have followed query that get data from 3 tables: index_data, data_bin and metrics.

SELECT 
    index_data.host_name,        
    data_bin.value
FROM
    metrics 
INNER JOIN
    index_data ON index_data.id = metrics.index_id 
INNER JOIN
    data_bin ON data_bin.id_metric = metrics.metric_id 
WHERE
    metrics.metric_name = 'avg';

Here are the results:

host_name      |  value
================================  
namenode1           1.125
namenode1           1
namenode1           1
namenode1           1
namenode1           1
namenode1           4.875
namenode1           1
namenode1           0.875
Centreon-Server     2
Centreon-Server     1
Centreon-Server     2
Centreon-Server     1
Centreon-Server     5
Centreon-Server     1
Centreon-Server     1
Centreon-Server     2

My goal is to get only one record per host_name with max value.

So the expected output should be:

 namenode1          4.875
 Centreon-Server    5

How can I achieve this?

Thanks,

Upvotes: 0

Views: 88

Answers (1)

Pragnesh Khalas
Pragnesh Khalas

Reputation: 2898

I think below will help you

SELECT 
     index_data.host_name,        
     Max(data_bin.value) as value
        FROM metrics 
     inner join index_data ON index_data.id = metrics.index_id 
     inner join data_bin   ON data_bin.id_metric = metrics.metric_id 

where metrics.metric_name = 'avg' AND  
FROM_UNIXTIME(data_bin.ctime) between date('2014-02-05 16:15:24') and date('2014-02-06 16:15:24') 
Group by index_data.host_name;

Upvotes: 1

Related Questions