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)
About the Author
Soniya Shah
Information Developer
Currently, a first year law student with a background in science and technology. Experienced technical writer, with specializations in software documentation, big data, blog development, and website development. I build user-centered content to communicate complex and technical information more easily.
I used to work for Vertica full time for about 3 years. I still work at Vertica part time while going to law school.
Update: Soniya is now doing her law internship, and no longer working at Vertica. Good luck, Soniya!
