CODEWITHSUNDEEP
mysqlsql
jan5
jan5

Reputation: 1179

Row with maximum values in mysql

I have table which looks like this

sales  units

100     1
200     3
100     2
200     4
100     5
100     1000

I want to select maximum sales with maximum units

for above example output should be 

 100 1000
 200 4

i tried to use max function it gives wrong answer e.g 200 1000

Upvotes: 0

Views: 90

Answers (3)

Guffa
Guffa

Reputation: 700362

Order by sales, then units, and take the first record:

select sales, units
from TheTable
order by sales desc, units desc
limit 1

Result:

sales  units
------ ------
200    4

Edit:

For the new output that you want, you need to group on the sales value, and use the max aggregate to get the highest units value in each group:

select sales, max(units)
from TheTable
group by sales
order by sales

Result:

sales  units
------ ------
100    1000
200    4

Upvotes: 1

Michał Powaga
Michał Powaga

Reputation: 23183

select sales, units
from tab
order by sales desc, units desc
limit 1

Since you have changed your question, the answer is:

select sales, max(units) as units
from tab
group by sales

Upvotes: 1

Sudhir Bastakoti
Sudhir Bastakoti

Reputation: 100175


select * from youtTable where sales =(select max(sales) from yourTable);

Upvotes: 2

Related Questions