Select top 2 rows in Hive

Question

I'm trying to retrieve top 2 tables from my employee list based on salary in hive (version 0.11). Since it doesn't support TOP function, is there any alternatives? Or do we have define a UDF?

score 73 · Accepted Answer · edited May 25 '15 at 17:35

73

Yes, here you can use LIMIT.

You can try it by the below query:

SELECT * FROM employee_list SORT BY salary DESC LIMIT 2

edited May 25 '15 at 17:35

Forseth11

1,418
1
12
21

answered May 25 '15 at 16:56

Akshay Shrivastava

754
6
2

1

@Akshay it works, i guess u missed the semicolon at the end – prashanth Jan 30 '18 at 18:33
2

Why sort by, why not order by? – Ashish Doneriya Nov 19 '18 at 10:04

score 4 · Answer 2 · answered Dec 25 '20 at 07:20

Here I think it's worth mentioning SORT BY and ORDER BY both clauses and why they different,

SELECT * FROM <table_name> SORT BY <column_name> DESC LIMIT 2

If you are using SORT BY clause it sort data per reducer which means if you have more than one MapReduce task it will result partially ordered data. On the other hand, the ORDER BY clause will result in ordered data for the final Reduce task. To understand more please refer to this link.

SELECT * FROM <table_name> ORDER BY <column_name> DESC LIMIT 2

Note: Finally, Even though the accepted answer contains SORT BY clause, I mostly prefer to use ORDER BY clause for the general use case to avoid any data loss.

score 3 · Answer 3 · answered Feb 09 '18 at 12:18

3

select * from employee_list order by salary desc limit 2;

answered Feb 09 '18 at 12:18

Ashish Doneriya

1,683
1
13
18

Select top 2 rows in Hive

3 Answers3

Linked