Top volume T-SQL

Question

Up above, I want to transform the first dataset into the latter. The goal is to order the Numbers by highest volume and then select the TOP 2 Letters among those numbers in descending order by Volume. Any solution in T-SQL? Thanks!

Answer 1

This is a classic limit-per-group problem, which can be easily solved using a common table expression and row_number().

First, create and populate sample table (Please save us this step in your future questions)

DECLARE @T as TABLE
(
    Number int,
    Letter char(1),
    Volume int
);

INSERT INTO @T (Number, Letter, Volume) VALUES
(1,'A',230),
(1,'B',534),
(1,'C',23),
(1,'D',42),
(2,'D',566),
(2,'E',24),
(2,'F',566); 
-- you can add more records but this should be enough for this demonstration

Then, use a common table expression with row_number() and max(...) over... for the groups and order by:

WITH CTE AS 
(
    SELECT  Number, 
            Letter, 
            Volume, 
            ROW_NUMBER() OVER(PARTITION BY Number ORDER BY Volume DESC) As rn,
            MAX(Volume) OVER(PARTITION BY Number) As MaxVolume,
    FROM @T
)

The query:

    SELECT Number, Letter, Volume
    FROM @T
    WHERE rn <= 2
    ORDER BY MaxVolume DESC

Results:

Number  Letter  Volume
2       D       566
2       F       566
1       B       534
1       A       230

You can see a live demo on rextester.