我有一个列id, cl_id, ratio, diff
的表,我想 Select 最大值为ratio
的观测值,按cl_id
分组,但如果有相等的值,那么应该从相等值中 Select 最大值diff
.
以下是Have
个和Want
个表格:
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);