Mysql - IMPALA query help needed -

i have 1 table in hive table1. using impala fetch data table

table1 ------ name, amount

where values of table are

test1, 10 test1, 15 test1, 30  test2, 30 test2, 40 test2, 50  test3, 30 test3, 40 test3, 50

now have fetch data table1 such that, fetch data name (test1, test2, test3) but gives top 2 records based on amount each name.

can possible in impala or in mysql?

thanks in advance

if you're using impala 2.0 or greater, can use analytic functions accomplish task:

select name, amount (select name, amount, row_number() on (partition name order amount desc) pos       table1) t pos < 3;

if must use mysql, appears can fake window functions using user-defined variables, demonstrated in another question on stackoverflow.

KBPS