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/9.2.x/HTML/Content/Authoring/AnalyzingData/Views/ViewExecution.htm
Have fun!
