Mysql Query problem ?

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) from sample group by name

You'll have problems getting the id, though, as there may be more than one.

Answer 2

you should group by NAME:

SELECT `name`,MAX(amt) from sample GROUP BY `name` where amt between 1000 and 5000

Answer 3

select id, name, amt from sample where amt = (select max(amt) from sample)

that should return all records that have the max amt from the sample table

edit:

select id, name, amt 
from sample 
where amt = (
  select max(amt) 
  from sample 
  where amt between 1000 and 5000)

this query will return all records that have an amount equal to the max amount between 1000 and 5000

Answer 4

If you only need ONE of the ids that contains the MAX(amt), then this will do the trick:

SELECT id, name, MAX(amt)
FROM sample
WHERE amt BETWEEN 1000 AND 5000
GROUP BY name;

If you need all the ids, then it gets more complicated. Two queries are required, plus a temporary table:

CREATE TEMPORARY TABLE maxamts
    SELECT name AS maxname, MAX(amt) AS maxamt
    FROM sample
    WHERE amt BETWEEN 1000 AND 5000
    GROUP BY maxname;

SELECT GROUP_CONCAT(id), maxname AS name, maxamt AS amt
FROM maxamts
LEFT JOIN sample ON ((maxname = sample.name) AND (maxamt = amt))
GROUP BY maxname;

In short: Create a temporary table from the query that finds each name/max(amt) pair, then use that temporary table to join back on the original table and pull out the IDs matching the name/amount combinations.

Just remember that group_concat is by default limited to 1,024 characters, (show variables like '%group_concat%' to see the max length on your server), so if you've got a large dataset, increase that limit, or remove the group_concat and group by from the second query and parse the information in your application.