
Vertica contains an abundance of built-in SQL analytic functions. One of the lesser known but also one of the coolest is the LIMIT analytic function.
Example
Say I have the following table data:dbadmin => SELECT * FROM limit_test;
the_date | test_num | test_desc
------------+----------+-----------
2018-01-10 | 1 | TEST1
2018-01-10 | 2 | TEST2
2018-01-12 | 1 | TEST
2018-01-11 | 2 | TEST2
2018-01-11 | 3 | TEST3
(5 rows)
But I only want to see the latest test number for each date. For that I can use the LIMIT analytic function!
dbadmin => SELECT *
dbadmin -> FROM (SELECT the_date, test_num, test_desc
dbadmin (> FROM limit_test
dbadmin (> LIMIT 1 OVER (PARTITION BY the_date order by test_num DESC)) foo
dbadmin -> ORDER BY the_date;
the_date | test_num | test_desc
------------+----------+-----------
2018-01-10 | 2 | TEST2
2018-01-11 | 3 | TEST3
2018-01-12 | 1 | TEST
(3 rows)