how to do this in MySql Query??

Question

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

Answer 1

SELECT 
    name,MAX(amt) AS amt
FROM 
    table 
WHERE 
    amt BETWEEN 1000 and 5000 
GROUP BY name

Here you're grouping by the name column, and retrieving the maximum value of the column 'amt' within that GROUP, for all of the rows where amt is between 1000 and 5000. This query is untested, but is simple enough - I believe it should work.

Answer 2

Select name,amt
from sample
where amt between 1000 and 5000
ORDER BY amt DESC
GROUP BY name