Vertica Quick Tip: The LIMIT Analytic Function

[Deleted User]

This tip was authored by Jim Knicely.

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:

=> 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!

=> SELECT * 
->   FROM (SELECT the_date, test_num, test_desc
(>           FROM limit_test
(>          LIMIT 1 OVER (PARTITION BY the_date order by test_num DESC)) foo
->  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)