The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

how to get the last element from the result of a SQL query in Vertica?

My problem is that how to get the result like this SQL query:

select max(code) from Table where code <= key;

Here code is the column name and the projection is order by code
I found that the MAX() aggregation is really really slow!!!!! Is there any method to solve it?

Another way is that 

select code from Table where code <= key ORDER BY DESC limit 1;
it is still very very slow!!!

Since the projection is order by code, the code I want to know is the last element of this query: 
select code from Table where code<=key;

but I do not know how to get the last element. Could you help me solve this problem? Thanks a lot!


  • Does the following work better?

    create table foo (a int) order by a;
    select a from (select a, lead(a,1) over (order by a asc) l from foo) b where l is null;

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file