How can I SELECT the max row in a table SQL?

Question

I have a little problem.

My table is:

Bill  Product ID Units Sold
----|-----------|------------
1   |    10     |      25
1   |    20     |      30
2   |    30     |      11
3   |    40     |      40
3   |    20     |      20

I want to SELECT the product which has sold the most units; in this sample case, it should be the product with ID 20, showing 50 units.

I have tried this:

SELECT 
    SUM(pv."Units sold")
FROM 
    "Products" pv
GROUP BY 
    pv.Product ID;

But this shows all the products, how can I select only the product with the most units sold?

What if there are ties (products with the same number of units sold) ? — wildplasser, Apr 23 '14 at 19:41
That is a good question... Let's suppose it won't happen (for the moment) But if it happens, would it be possible to show both products? — carlos, Apr 23 '14 at 19:43

Aleks G · Answer 1 · 2014-04-23T20:02:04.370

3

Leaving aside for the moment the possibility of having multiple products with the same number of units sold, you can always sort your results by the sum, highest first, and take the first row:

SELECT pv."Product ID", SUM(pv."Units sold")
FROM "Products" pv
GROUP BY pv."Product ID"
ORDER BY SUM(pv."Units sold") DESC
LIMIT 1

I'm not quite sure whether the double-quote syntax for column and table names will work - exact syntax will depend on your specific RDBMS.

Now, if you do want to get multiple rows when more than one product has the same sum, then the SQL will become a bit more complicated:

SELECT pv.`Product ID`, SUM(pv.`Units sold`)
FROM `Products` pv
GROUP BY pv.`Product ID`
HAVING SUM(pv.`Units sold`) = (
    select max(sums)
    from (
          SELECT SUM(pv2.`Units sold`) as "sums"
          FROM `Products` pv2
          GROUP BY pv2.`Product ID`
    ) as subq
)

Here's the sqlfiddle

edited Apr 23 '14 at 20:02

answered Apr 23 '14 at 19:44

Aleks G

56,435
29
168
265

+1 and for the quotes issue, see my answer to [Do different databases use different name quote?](http://stackoverflow.com/questions/214309/do-different-databases-use-different-name-quote/214344#214344) – Bill Karwin Apr 23 '14 at 19:45
Have you tried `MAX(SUM(...))`? I don't think that works in a single "level" of subquery. – Bill Karwin Apr 23 '14 at 19:48
@BillKarwin Cool, thanks for the double-quote link. I personally try to avoid spaces in the table/column names though. – Aleks G Apr 23 '14 at 19:48
@BillKarwin You are right, `max(sum(...))` doesn't work. I updated my answer with the correct query. – Aleks G Apr 23 '14 at 20:03

score 1 · Answer 2 · answered Apr 23 '14 at 19:50

1

SELECT SUM(pv."Units sold") as `sum`
   FROM "Products" pv
   group by pv.Product ID
ORDER BY sum DESC
LIMIT 1

limit 1 + order by

answered Apr 23 '14 at 19:50

Павел Иванов

1,124
8
23

score 0 · Answer 3 · answered Aug 10 '17 at 12:44

The Best and effective way to this is Max function

Here's The General Syntax of Max function

SELECT MAX(ID) AS id
FROM Products;

and in your Case

SELECT MAX(Units Sold) from products

Here is the Complete Reference to MIN and MAX functions in Query Click Here

How can I SELECT the max row in a table SQL?

3 Answers3