Sort Data from Within a View
Jim_Knicely
- Select Field - Administrator
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!
0
Comments
Hi,
Can you tell us if this is applicable to older versions of vertica as well? or only compatible with 9.2.x onwards ?
Regards
Goes back to at least Vertica 8.1!
See:
https://www.vertica.com/docs/8.1.x/HTML/index.htm#Authoring/AnalyzingData/Views/ViewExecution.htm