CODEWITHSUNDEEP
sqlsql-server
user3005371
user3005371

Reputation: 3

SQL - Peak value per group from single table

I am having trouble finding an answer to this one, so hoping someone may be able to help.

I am trying to do the following. I have a table with 4 columns and 22000+ records. Of these 22000+ records, there are 335 distinct server hostnames. Each record represents a peak value of a given metric and the date that corresponds to that. The problem I am having is bringing back the peak value for each server (instead of all records).

Example of the source data

enter image description here

What I would like to achieve (using the subset above as an example) is as follows

enter image description here

Is this something that can be done easily with a query?

Thanks for looking and I look forward to seeing any replies.

AF

Upvotes: 0

Views: 233

Answers (5)

conan
conan

Reputation: 124

Taking into account the peak could occur more than once:

SELECT final.hostname
    ,final.metric
    ,MAX(final.peak_d) AS last_occurred
    ,final.peak
FROM (
    SELECT t.hostname
        ,t.metric
        ,t.Peak_D
        ,t.Peak
    FROM table1 t
    JOIN (
    SELECT i.hostname
            ,i.metric
            ,Max(i.Peak) AS MaxPeak
        FROM table1 i
        GROUP BY i.hostname
            ,i.metric
        ) AS pre ON t.hostname = pre.hostname
        AND t.metric = pre.metric
        AND t.peak = pre.MaxPeak
    ) AS final
GROUP BY final.hostname
    ,final.metric
    ,final.peak

Upvotes: 0

LeeWallen
LeeWallen

Reputation: 98

What about doing a group by host name, and select the max peak value. ie,

select hostname, metric, peak_d, MAX(peak) as max_peak from sometable group by hostname;

This is similar to other answers below, but there is no need for sub queries or CTEs to do what you are asking. Perhaps there is more to your question that might warrant doing joins on other tables, but I don't really see a need to do that based on the information you provided.

Upvotes: 0

E.K.
E.K.

Reputation: 419

with cte as (
    select 
            hostname, metric, peak_d, peak
            ,row_number() over(partition by hostname order by peak desc) as OrderWithinGroup
        from Table1
)
select hostname, metric, peak_d, peak
    from cte
    where OrderWithinGroup = 1
    order by hostname;

Upvotes: 1

Jayadevan
Jayadevan

Reputation: 1342

select * from mytable where (hostname, peak) in ( select hostname,max(peak) from tablename group by hostname)

Upvotes: 0

Jack Jiang
Jack Jiang

Reputation: 522

SELECT t1.HOSTNAME, t1.METRIC, t1.PEAK_D, t1.PEAK
FROM Table t1
JOIN
(
    SELECT HOSTNAME, MAX(PEAK) AS MaxPeak
    FROM Table
    GROUP BY HOSTNAME
) t2 ON t1.HOSTNAME = t2.HOSTNAME
     AND t1.PEAK = t2.MaxPeak

Upvotes: 0

Related Questions