Sort Data from Within a View

Jim_Knicely

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!

RaghavA

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

Jim_Knicely

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