Getting the Rows that have the maximum (or minimum) value for a Column within a partition
Another scenario easily done through the ranking functions, is finding the row that contains the max value in a partition
Returning to our original input data set, imagine we want to partition by Vertical and within each vertical find the row that has the maximum value for latency.
The desired output for is as follows. As clearly 500 is the maximum latency in both Image and Web
The U-SQL that accomplishes this uses ROW_NUMBER
@results = SELECT Query, Latency, Vertical, ROW\_NUMBER() OVER (PARTITION BY Vertical ORDER BY Latency DESC) AS rn FROM @querylog; @results = SELECT Query, Latency, Vertical FROM @results WHERE rn==1;
To retrieve the row with the minimum value for each partition, in the OVER clause change the DESC to ASC.