Teradata - Max value of a dataset with corresponding date

Question

This is probably obvious, I just can't seem to get it to work right. Let's say I have a table of various servers and their CPU percentages for every day for the past year. I want to basically say:

"for every server name, show me the max CPU value that this server hit (from this dataset) and the corresponding date that it happened on"

So ideally I would get a result like:

server1 52.34% 3/16/2012
server2 48.76% 4/15/2012
server3 98.32% 6/16/2012
etc..

When I try to do this like so, I can't use a group by or else it just shows me every date:

select servername, date, max(cpu) from cpu_values group by 1,2 order by 1,2;

This of course just gives me every server and every date.. Sub-query? Partition by? Any assistance would be appreciated!

Answer 1

You can use the row_number() OLAP window function:

   select   servername
          , cpu
          , date 
   from     cpu_values
   qualify  row_number() over (partition by servername
                               order by cpu desc) = 1

Notice that you do not need a GROUP BY or ORDER BY clause. The PARTITION clause is similar to a GROUP BY and the ORDER BY clause sorts the rows within each partition (in this case by descending cpu). The "=1" part selects the single row that satisfies the condition.

Answer 2

A subquery would be the simplest solution:

SELECT
    S.Name, Peak.PeakUsage, MIN(S.Date) AS Date
FROM
    ServerHistory AS S
    INNER JOIN
        (
        SELECT
            ID, MAX(CPUUsage) AS PeakUsage
        FROM
            ServerHistory
        WHERE
            Date BETWEEN X AND Y
        GROUP BY
            ID
        ) AS Peak ON S.ID = Peak.ID
GROUP BY
    S.Name, Peak.PeakUsage

P.S., next time around, you may want to tag with "SQL". There are relatively few Teradata people out there, but plenty who can help with basic SQL questions.