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

User · Accepted Answer

Yes  here you can use LIMIT   You can try it by the below query   SELECT   FROM employee list SORT BY salary DESC LIMIT 2

User · Answer

select   from employee list order by salary desc limit 2

User · Answer

Here I think it s worth mentioning SORT BY and ORDER BY both clauses and why they different  SELECT   FROM  lt table name gt  SORT BY  lt column name gt  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  lt table name gt  ORDER BY  lt column name gt  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

[hadoop] Select top 2 rows in Hive

Examples related to hadoop

Examples related to hive

Examples related to hiveql