Keep every row tied for first place

A correlated maximum expresses the requirement directly. Optimizers may decorrelate it, so measure rather than assuming the window query is always faster.

Time: Potential O(n²) without optimization or indexes; inspect the query plan · Space: Engine dependent

SELECT s.id,s.customer,s.amount FROM sales s
WHERE s.amount IS NOT NULL AND s.amount=(
 SELECT MAX(t.amount) FROM sales t WHERE t.customer=s.customer
) ORDER BY s.customer,s.id;

ROW_NUMBER assigns a distinct position, RANK leaves gaps after ties, and DENSE_RANK does not. To keep all highest-value sales per customer, rank each partition by amount descending and filter in a CTE. Exclude null amounts before ranking so an all-null customer has no winner. Window ordering determines rank; outer ORDER BY determines displayed row order.

Time: Typically O(n log n) for partition sorting; inspect the actual plan. · Space: Up to O(n) sort/window workspace; engine dependent.

WITH ranked AS (
  SELECT id, customer, amount,
    DENSE_RANK() OVER (PARTITION BY customer ORDER BY amount DESC) AS position
  FROM sales WHERE amount IS NOT NULL
)
SELECT id, customer, amount FROM ranked
WHERE position = 1 ORDER BY customer, id;