Sort Data from Within a View

Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert admin
edited December 2018 in Vertica Tips

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!

Comments

Sign In or Register to comment.