ID NAME AMT
1 Name1 1000
2 Name2 500
3 Name3 3000
4 Name1 5000
5 Name2 2000
6 Name1 3000

consider above table as sample.

am having a problem in my sql query, Am using like this.

Select name,amt from sample where amt between 1000 and 5000

it returns all the values in the table between 1000 and 5000, instead I want to get maximum amount record for each name

i.e.,

3 name3 3000
4 name1 5000
5 name2 2000