Group by in mysql with order by

Question

I have a table structure like this:

Stock Table

id  stock
1   0
2   113
3   34
4   50

Products Table (Both tables are connected with ID column)

id   parid   name
1    1       A
2    1       B
3    2       C
4    3       D

I'm using Group by parid in my query which is a requirement of my project.

Query:

select * 
from products as p inner join 
     stock as s on p.id=s.id 
group by parid 
order by stock DESC

It displays result as:

id  parid   name
4   3       D
3   2       C
1   1       A

What I want is:

Group by should include the products according to the stock of product in descending order.

So query should fetch below result rather than the above one:

id  parid  name
4   3      D
3   2      C
2   1      B

Any help will be appreciated.

This may be an answer

Using the below two answers, I came up with the below query which presently seems to solve the problem (still need to check with multiple cases).

SELECT * FROM products p inner join stock i on p.id=i.id inner join (select max(stock) maxstock,parid from products inner join stock on products.id=stock.id group by parid) q on q.parid=p.parid and q.maxstock=i.stock group by p.parid

Answer 1

Consider the following intermediate result:

SELECT p.*
     , s.stock
  FROM products p
  JOIN stock s
    ON s.id = p.id
+------+-------+------+-------+
| id   | parid | name | stock |
+------+-------+------+-------+
|    1 |     1 | A    |   113 |
|    2 |     1 | B    |   113 |
|    3 |     2 | C    |     0 |
|    4 |     3 | D    |    50 |
+------+-------+------+-------+

What is the criteria for choosing row id 2 rather than row id 1?

Based upon your comments to date, I maintain that my previous answer is correct. If it's wrong, it's because your articulation of the problem is wrong.

SELECT p.*
     , s.stock
  FROM products p
  JOIN stock s
    ON s.id = p.id
  JOIN 
     ( SELECT p.parid
            , MAX(stock) max_stock
         FROM products p
         JOIN stock s
           ON s.id = p.id
        GROUP
           BY p.parid
     ) y
    ON y.parid = p.parid
   AND y.max_stock = s.stock;

Extending this idea to resolve ties, we can use another technique...

SELECT a.*
  FROM 
     ( SELECT p.*
            , s.stock
         FROM products p
         JOIN stock s
           ON s.id = p.id
         JOIN 
            ( SELECT p.parid
                   , MAX(stock) max_stock
                FROM products p
                JOIN stock s
                  ON s.id = p.id
               GROUP
                  BY p.parid
            ) y
           ON y.parid = p.parid
          AND y.max_stock = s.stock
     ) a
  LEFT
  JOIN
     ( SELECT p.*
            , s.stock
         FROM products p
         JOIN stock s
           ON s.id = p.id
         JOIN 
            ( SELECT p.parid
                   , MAX(stock) max_stock
                FROM products p
                JOIN stock s
                  ON s.id = p.id
               GROUP
                  BY p.parid
            ) y
           ON y.parid = p.parid
          AND y.max_stock = s.stock
     ) b
    ON b.parid = a.parid
   AND b.id < a.id
 WHERE b.id IS NULL;
+------+-------+------+-------+
| id   | parid | name | stock |
+------+-------+------+-------+
|    1 |     1 | A    |   113 |
|    3 |     2 | C    |     0 |
|    4 |     3 | D    |    50 |
+------+-------+------+-------+

Answer 2

Try running this:

select * 
from products as p inner join 
     stock as s on p.id=s.id 
where stock in (select max(stock) from products as p inner join 
     stock as s on p.id=s.id  group by parid)
group by parid 
order by stock desc