Select max values, but when ties, select max from other column

Question

I have a table with columns id, cl_id, ratio, diff and I want to select the observations with max ratio, grouped by cl_id, but if there are equal values, then it should be selected the max diff from the equals.

Below are the Have and Want tables:

CREATE TABLE Have(
    id INT PRIMARY KEY,
    cl_id CHAR(1),
    ratio DECIMAL(5,4),
    diff INT
);

INSERT INTO Have(id, cl_id, ratio, diff) VALUES 
(122222, 'a', 1.3333, 2),
(123333, 'a', 1.3333, 5),
(124444, 'b', 1.25, 2),
(125555, 'b', 1.6667, 1);


CREATE TABLE Want(
    id INT PRIMARY KEY,
    cl_id CHAR(1),
    ratio DECIMAL(5,4),
    diff INT
);

INSERT INTO Want(id, cl_id, ratio, diff) VALUES 
(123333, 'a', 1.3333, 5),
(125555, 'b', 1.6667, 1);

Answer 1

We can use ROW_NUMBER() here with a two level sort:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY cl_id ORDER BY ratio DESC, diff DESC) rn
    FROM Have
)

SELECT id, cl_id, ratio, diff
FROM cte
WHERE rn = 1;