Sort Data from Within a View: Quick Tip

Posted December 11, 2018 by Jim Knicely, Big Data Solutions Architect, Vertica

If a view definition includes an ORDER BY clause, Vertica ignores it. But you can force Vertica to sort the view data on the ORDER BY columns by adding a LIMIT clause. If you don’t know how many records to limit, simply limit by some arbitrary large value! Example: dbadmin=> SELECT * FROM view_fact_table; c1 | c2 ------+---------- 1 | TEST1 15 | TEST15 100 | TEST100 1000 | TEST1000 2 | TEST2 (5 rows) dbadmin=> CREATE VIEW some_view AS dbadmin-> SELECT * dbadmin-> FROM view_fact_table dbadmin-> ORDER BY c1; CREATE VIEW dbadmin=> SELECT * FROM some_view; c1 | c2 ——+———- 1 | TEST1 15 | TEST15 100 | TEST100 1000 | TEST1000 2 | TEST2 (5 rows) dbadmin=> CREATE OR REPLACE VIEW some_view AS dbadmin-> SELECT * dbadmin-> FROM view_fact_table dbadmin-> ORDER BY c1 dbadmin-> LIMIT 100000000; CREATE VIEW dbadmin=> SELECT * FROM some_view; c1 | c2 ——+———- 1 | TEST1 2 | TEST2 15 | TEST15 100 | TEST100 1000 | TEST1000 (5 rows) Helpful Link: https://www.vertica.com/docs/latest/HTML/Content/Authoring/AnalyzingData/Views/ViewExecution.htm Have fun!