CODEWITHSUNDEEP
mysqlsql
jan5
jan5

Reputation: 1179

retriving all rows with maximum value

I have a query like this:

SELECT state AS ls,count(*) AS total, max(sales) AS ye FROM TABLE
GROUP BY state

Then I get the following result:

A total ye
----------
a  22   23
b  23   23
c  24   21
d  25   21

But I want to display only state having maximum value of ye, i.e:

A total ye
----------
a  22   23
b  23   23

Upvotes: 1

Views: 187

Answers (4)

Somnath Muluk
Somnath Muluk

Reputation: 57806

Try this query:

select * from (select state as ls,count(*) as total ,max(sales) as ye from  table    
group by state)temp HAVING max( ye );

Upvotes: 1

user359040
user359040

Reputation:

Try:

select state as ls,count(*) as total, max(sales) as ye from  table    
group by state
having max(sales) = (select max(sales) from table)

Upvotes: 3

Vikram
Vikram

Reputation: 8333

select state as ls,count(*) as total ,max(sales) as ye from  table    
where sales = (select max(sales) from table)
group by state

Upvotes: 2

klennepette
klennepette

Reputation: 3196

You can use the HAVING clause for this, it's similar to WHERE Only it's executed after all data is retrieved. You can specify it after group by like:

select state as ls,count(*) as total ,max(sales) as ye from  table    
group by state
having count(*) = max(sales)

Upvotes: 4

Related Questions