Suppose I have the following table:

And want to order by Key-groups with the highest values and then by the value in descending order, as:

EDIT 1: when there is multiple groups with the same highest value, then the second highest would determine the order of the groups.

How can I accomplish this in SQL Server?

CodePudding user response：

It's not clear from the comments if you have the solution you need, but the following ordering criteria should give your resired result:

select * 
from t
order by Max([value]) over(partition by [key]) desc, [key], [value] desc;

Demo fiddle

CodePudding user response：

This is actually more complicated than it looks, because you want to sort each group by its individual items.

Aside from some complex string aggregation, the only way I can see to make this work the way you want using just window functions, is to calculate a running sum per Key, then sum the running sum.

WITH cte1 AS (
    SELECT *,
      runningSum = SUM(t.V) OVER (PARTITION BY t.K ORDER BY t.V DESC ROWS UNBOUNDED PRECEDING)
    FROM t
),
cte2 AS (
    SELECT *,
      totalRunning = SUM(t.runningSum) OVER (PARTITION BY t.K)
    FROM cte1 t
)
SELECT *
FROM cte2 t
ORDER BY
  totalRunning DESC,
  K ASC,
  V DESC;

You can obviously simplify this down if you don't want to see the workings

WITH cte1 AS (
    SELECT *,
      runningSum = SUM(t.V) OVER (PARTITION BY t.K ORDER BY t.V DESC ROWS UNBOUNDED PRECEDING)
    FROM t
)
SELECT K, V
FROM cte2 t
ORDER BY
  SUM(t.runningSum) OVER (PARTITION BY t.K) DESC,
  K ASC,
  V DESC;

db<>fiddle

If the groups each can have different counts of items then this will not work.