CODEWITHSUNDEEP
sqlhiveimpala
mannaroth
mannaroth

Reputation: 1543

Choosing the second to last value in Impala/HIVE/SQL

I would like to get the second to last item of a list in Impala. Is there a function/method that would return (min+1)(x)?

Example table:

foo   bar
 1     3
 2     5
 3     6
 4     2
 5     8

The function

SELECT MIN(bar) FROM example

will return

bar
 2

I would like to select instead the second to last value from the list of numerical values in bar:

SELECT SECONDTOLAST(bar) FROM example

returning

bar
 3

Upvotes: 1

Views: 572

Answers (1)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use row_number() function :

select t.bar
from (select *, row_number() over (order by bar) as seq
      from table
     ) t
where seq = 2; -- Get the second last bar

Upvotes: 2

Related Questions