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