CODEWITHSUNDEEP
hadoophive
Ajay
Ajay

Reputation: 795

Hive - Select unique rows based on some key field

I am using Hive 1.2.1 and want to select unique rows based on empid

empid  empname  dept 
101    aaaa     dept1
101    aaaa     dept2
102    bbbb     dept1
103    cccc     dept2

I tried to use correlated subquery but that does not work

select empid, 
       empname,
    dept
       (select count(*) 
        from emp t2 
        where t2.empid = t1.empid) as row_number
from emp t1 where row_number=1
order by empid;

Is there a way to select unique value based on some key field? Need your help..

Expected output would be

empid  empname  dept 
101    aaaa     dept1
102    bbbb     dept1
103    cccc     dept2

Thanks.

Upvotes: 0

Views: 1430

Answers (1)

alexeipab
alexeipab

Reputation: 3619

If you need a single row per unique key, than you can use row_number():

select empid, empname, dept from (
select row_number() over (partition by empid order by empname , dept) as rowNum, empid, empname, dept from table
) q where rowNum == 1

Upvotes: 1

Related Questions