SQL Server custom sort while grouping by max sum

Question

I have 3 columns (Year, Number, SUM(Amount)) and I am trying to sort them by the max sum of amount.

SELECT TOP 1000 
    YEAR(period) AS [Year], id_number, 
    SUM(ISNULL(amount, 0)) AS [Amount]
FROM 
    table
WHERE 
    (YEAR(period) >= 2010 AND YEAR(period) < 2021)
GROUP BY 
    YEAR(period), id_number
ORDER BY 
    SUM(ISNULL(amount, 0)) DESC, id_number, YEAR

This is not the sorting I'm trying to achieve. I'd like to group them together by their id_number but sort by the max amount for all years returned. I'm guessing I might have to write a case statement but I haven't figured it out yet. I Will update if I do. I wanted to ask for help also before I rack my mind for hours on this one.

Thank you very much in advance.

Answer 1

Based in your description, you want to sort the ids by the maximum sum in any year. If that is the case, use window functions in the ORDER BY:

SELECT TOP 1000 YEAR(period) AS [Year], id_number, 
       SUM(ISNULL(amount, 0)) AS [Amount]
FROM table
WHERE YEAR(period) >= 2010 AND YEAR(period) < 2021
GROUP BY YEAR(period), id_number
ORDER BY MAX(SUM(ISNULL(amount, 0))) OVER (PARTITION BY id_number),
         id_number, YEAR;

The second sort key is needed so all rows for a given id_number() are together when there are ties.

Answer 2

I think this should be sufficient for you. Just wrap it in a subquery and youre good to go

SELECT * 
FROM 
    (
        SELECT TOP 1000 
            YEAR(period) AS [Year], id_number, 
            SUM(ISNULL(amount, 0)) AS [Amount]
        FROM 
            table
        WHERE 
            (YEAR(period) >= 2010 AND YEAR(period) < 2021)
        GROUP BY 
            YEAR(period), id_number
    ) x
ORDER BY 
    MAX(ISNULL(amount, 0)) DESC, id_number, YEAR

Answer 3

You can reference the column by index like:

SELECT TOP 1000 
    YEAR(period) AS [Year], id_number, 
    SUM(ISNULL(amount, 0)) AS [Amount]
FROM 
    table
WHERE 
    (YEAR(period) >= 2010 AND YEAR(period) < 2021)
GROUP BY 
    YEAR(period), id_number
ORDER BY 
    3 DESC, id_number, YEAR

Answer 4

Thank you much @Doug, I'm going to run that down and try it out. I also came up with a solution that might be a bit nutty but it works. LOL.

SELECT t2.row, YEAR(period) AS [Year], t1.id_number, SUM(ISNULL(amount,0)) AS [Consumption]
FROM table t1 JOIN 
(SELECT id_number, ROW_NUMBER() over (order by SUM(ISNULL(amount,0))desc) as row
FROM table
WHERE (YEAR(period) >= 2010 AND YEAR(period) < 2021)
GROUP BY id_number) t2 ON t1.id_number = t2.id_number
WHERE (YEAR(period) >= 2010 AND YEAR(period) < 2021)
GROUP BY t2.row, YEAR(period), t1.id_number
ORDER BY t2.row, year

@Larnu, I will look at fixing this. Thank you.