让我们设置示例数据:
CREATE TABLE public.test (
A int,
B int,
C varchar,
D int,
E int
);
INSERT INTO public.test (A, B, C, D, E) VALUES (1, 2, 'AAA', 4, 1404305559);
INSERT INTO public.test (A, B, C, D, E) VALUES (1, 2, 'BBB', 23, 1404305633);
INSERT INTO public.test (A, B, C, D, E) VALUES (1, 2, 'CCC', 62, 1404305705);
INSERT INTO public.test (A, B, C, D, E) VALUES (1, 2, 'AAA', 123, 1404305740);
INSERT INTO public.test (A, B, C, D, E) VALUES (1, 2, 'BBB', 91, 1404305778);
COMMIT;
我们将使用RANK https://my.vertica.com/docs/7.0.x/HTML/index.htm#Authoring/SQLReferenceManual/Functions/Analytic/RANKAnalytic.htm函数对每行进行排名A, B, C
并排序E
并仅返回顶部的行(排名为 1)。
SELECT a.a,
a.b,
a.c,
a.d,
a.e
FROM (SELECT a,
b,
c,
d,
e,
RANK()
OVER (
PARTITION BY a, b, c
ORDER BY e DESC) AS rank
FROM public.test) a
WHERE a.rank = 1;
这将返回:
A | B | C | D | E
---+---+-----+-----+------------
1 | 2 | CCC | 62 | 1404305705
1 | 2 | AAA | 123 | 1404305740
1 | 2 | BBB | 91 | 1404305778