. @Suresh Bonam yes you can calculate the nth element, see the following example:
Create sample table (emp=employees)
create table emp(id Int, salary Double)
row format delimited
fields terminated by ','
stored as textfile
I have added the following data to that table:
1,423
2,23
3,67878
4,84
5,9999999
6,1
7,578
8,8877
9,4445
10,756
11,84
Understanding the data
Lets look at the sorted salaries first:
select * from emp order by salary;
Result:
emp.id emp.salary
6 1
2 23
4 84
11 84
1 423
7 578
10 756
9 4445
8 8877
3 67878
5 9999999
Note: We have two identical salaries in the table (=84)
Create query to select unique salaries
In order to select the nth-element we need to remove the duplicate values (e.g. distinct) and create some kind of row counter or index. I decided to use Group-By to remove the duplicate salaries and row_number-UDF to generate the index.
select salary, ROW_NUMBER() over (ORDER BY salary) as row_no from emp group by salary
Result:
salary row_no
1 1
23 2
84 3
423 4
578 5
756 6
4445 7
8877 8
67878 9
9999999 10
Putting everything together
select * from (
select salary, ROW_NUMBER() over (ORDER BY salary) as row_no from emp group by salary) res
where res.row_no = 4
Result:
res.salary res.row_no
423 4
Note: We received the correct result and not 84 since we've removed the duplicate salaries in the subquery 🙂
... View more
