CODEWITHSUNDEEP
mysqlsql
MarathonStudios
MarathonStudios

Reputation: 2979

Select the maximum for each value in a MySQL table

I have a MySQL table with a list of STATEs and ORDERAMOUNTs:

State | OrderAmount
===================
New York  5
New York  6
Pennsylvania  6
Pennsylvania  7

I want to select a list of all states that have any single order LARGER then a given amount, say 15, as well as the largest order amount for that state. How would I iterate through all STtate values to get this list using a single query?

Upvotes: 2

Views: 95

Answers (2)

Tudor Constantin
Tudor Constantin

Reputation: 26861

Try something like:

Select s1.state, s1.orderamount, MAX(s2.orderamount) AS max_amount FROM states s1 
LEFT JOIN states s2 ON s1.id = s2.id
WHERE s1.orderamount > 15 GROUP BY s1.id

Upvotes: 0

Mike Purcell
Mike Purcell

Reputation: 19979

Untested, but the following should work:

SELECT state, max(orderamount) AS amount FROM table GROUP BY state HAVING amount > 15;

Upvotes: 3

Related Questions