Search code examples
sqlhiveemr

Hive: How to select the middle element order by some column

What is the hive query to select the middle element ordered by some column.

Example:

Name      age
A          10
B          20
C          30

Output: B 20.

Solution

  • You can find the middle row using analytic functions row_number() and count() like so:

    select name, age
from (
select
    name,
    age,
    row_number() over (order by your_order_by_list) r,
    count(*) over () c
from
    your_table) t
where r = cast((c + 1) / 2 as int);