0

I want top 2 valus per key. The result would look like:

enter image description here

What should be the hive query.

1 Answers1

0

You can use a window function with OVER() close:

select col1,col2 from (SELECT col1,
col2, 
ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col2 DESC) AS row_num
FROM data)f
WHERE f.row_num < 3
order by col1,col2
Ereli
  • 965
  • 20
  • 34
  • Suppose I have another column col3 and I want to display the value of col3. So I am writing following code: select col1,col3,col2 from (SELECT col1,col3, col2, ROW_NUMBER() OVER (PARTITION BY col1,col3 ORDER BY col2 DESC) AS row_num FROM data)f WHERE f.row_num < 3 order by col1,col2 But I am getting error. How to solve this –  Jun 21 '18 at 19:54
  • please share the error and an example of the table and version you're using. – Ereli Jun 21 '18 at 20:08
  • Table: CREATE TABLE data ( Id Serial, col1 VARCHAR(70) NOT NULL, col3 varchar(70), col2 integer, PRIMARY KEY(Id) ); INSERT INTO data (col1,col3, col2) VALUES ('a','p', '43'), ('a','p', '11'), ('b','q', '65'), ('b','q', '33'), ('c','r', '11'), ('c','r', '22'), ('c','r', '33'), ('a','p', '55') ; Query: select col1,col2,col3 from (SELECT col1, col2, col3 ROW_NUMBER() OVER (PARTITION BY col1,col3 ORDER BY col2 DESC) AS row_num FROM data)f WHERE f.row_num < 3 order by col1,col2 –  Jun 21 '18 at 20:12
  • Error: Query Error: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '() OVER (PARTITION BY col1,col3 ORDER BY col2 DESC) AS row_num FROM data)f WHERE' at line 2 –  Jun 21 '18 at 20:12
  • you're missing a comma after col3 https://www.db-fiddle.com/f/mx2NaLgzvYLvq3gGLkYvZG/0 – Ereli Jun 21 '18 at 20:21
  • After adding comma It's showing same error. https://www.db-fiddle.com/f/6QXPWbvt9XwZTpbsecBYeL/3 –  Jun 21 '18 at 20:26
  • try this https://www.db-fiddle.com/f/6QXPWbvt9XwZTpbsecBYeL/4 with postgres. closer to hive – Ereli Jun 21 '18 at 20:31
  • Can I run this query on hive ? –  Jun 21 '18 at 20:34
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/173581/discussion-between-ereli-and-kallol). – Ereli Jun 21 '18 at 20:36