Hive: Select rows with max value from a column

Question

I want to select all rows for which the timestamp column has the maximum value. The data looks like this:

A      B      timestamp
john   smith   2018
bob    dylan   2018
adam   levine  2017
bob    dylan   2017

The result should be:

A      B      timestamp
john   smith   2018
bob    dylan   2018

With Impala, the following SQL Query works: SELECT * FROM table WHERE timestamp=(SELECT Max(timestamp) from table)

But with Hive, the SQL Query doesn't.

Solution

Please always include the error message.

Try with

SELECT * FROM table WHERE timestamp IN (SELECT Max(timestamp) from table)

Comments

[Mehmet Ates]

Thanks a lot! Now it works. The problem was not Hive, but the Cloudera Frontend Hue, which threw errors, when I typed it.