CODEWITHSUNDEEP
hiveqlimpala
keerthi
keerthi

Reputation: 1

how to select multiple column from the table using group by( based on one column) , having and count in hive query

Requirement :
Using group by A and get records having count > 1
eg:

SELECT count(sk), id, sk
FROM table x
GROUP BY id
HAVING COUNT(sk) > 1

But I am not able to select sk in select statement. Is there any other way to do this. how to use partition on this input and output set attached here?

Upvotes: 0

Views: 131

Answers (1)

yahoo
yahoo

Reputation: 331

Something like this, you can do.

select * from (
SELECT count(sk)over(partition by id) as cnt, id, sk
FROM table x) a
where a.cnt >1

Upvotes: 1

Related Questions