Group by in mysql with order by

Question

I have a table structure like this:

Stock Table

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

What is the relation between those tables? JOIN on s.id=p.id? — Raging Bull, Jun 08 '15 at 09:08
Relation is One is products table & the other is stock table. For every productid, there is stock in stock table. — Shanil Soni, Jun 08 '15 at 09:09
Yeah, looks like a minor typo. What is `parid`? Maybe if I understood it better, it could help me come up with a solution. — Blue, Jun 08 '15 at 09:09
@ShanilSoni: What I am asking is that the relation might be `p.parid=s.id`, right? — Raging Bull, Jun 08 '15 at 09:11
I'm sorry for the misunderstanding. I assign `parid` for variant point of view. One product can have three different variants i.e. 100ml, 200ml, 500ml, each of these must have same `parid` in order to work properly. — Shanil Soni, Jun 08 '15 at 09:15
Your Products table has two entries with parid=1. Which id and which name is supposed to be displayed in the table you want? — wallenborn, Jun 08 '15 at 09:18
@ShanilSoni: So, how will product 3 will be in first position as in your desired result? It only has 34 in stock while product 4 has 50? — Raging Bull, Jun 08 '15 at 09:21
Sorry for the mistake from my end, check the result query is producing. I edited it just now. — Shanil Soni, Jun 08 '15 at 09:24
@ShanilSoni: No, I am asking about the desired result. You have updated the other one. — Raging Bull, Jun 08 '15 at 09:25
This is a fun one. Giving me a real headache, but I'm working on it. — Blue, Jun 08 '15 at 09:27
Please check now @RagingBull Sorry for the headache, I might not have presented it properly — Shanil Soni, Jun 08 '15 at 09:28
I'm so sorry, I made a lot of errors while putting out the question. This is now the final one. — Shanil Soni, Jun 08 '15 at 09:38
Change your query @ShanilSoni. It should be `stock as s on i.id=s.id` => `stock as s on p.id=s.id` (There is no `i` alias) — Blue, Jun 08 '15 at 09:40
@ShanilSoni Have a look [here](http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group) if you're concerned about performance. While Nikhil's answer does work, it may not be the most efficient query in this circumstance. — Blue, Jun 08 '15 at 09:48

Nikhil Batra · Answer 1 · 2015-06-09T06:19:32.610

3

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

edited Jun 09 '15 at 06:19

answered Jun 08 '15 at 09:30

Nikhil Batra

3,058
12
19

I don't think there is any straightforward approach to solve this. It is just an initial, I am trying to come up with something better if possible. – Nikhil Batra Jun 08 '15 at 09:34
It's ugly, but this is the solution that meets his needs ATM – Blue Jun 08 '15 at 09:34
I think yes you understood me what I'm trying to say, this may solve the issue. – Shanil Soni Jun 08 '15 at 09:34
1

Interesting approach to this puzzle. This does appear to be the solution @ShanilSoni. Be sure to accept it if it works for you. [Here](http://sqlfiddle.com/#!9/6705f/42) is a fiddle of his answer if you want to check it out for yourself. – Blue Jun 08 '15 at 09:38
Kudo's Nikhil, this was a fun problem. – Blue Jun 08 '15 at 09:42
@NikhilBatra What it does is, no matter what `max(stock)` is, it always displays first product in group by statement. It fetches `max(stock)` correctly, but still the product with stock 0 comes up in this query. – Shanil Soni Jun 09 '15 at 03:51
With the two answers, I was able to come up with a query. Added to my question. – Shanil Soni Jun 09 '15 at 04:26
1

In my case it is running fine, don't know why it isn't working in your case. The sole purpose of the inner query was to return maximum stock value based on the grouping parameter i.e. parid. So I believe it should have worked. But if you find the solution then good for you. Cheers :) – Nikhil Batra Jun 09 '15 at 06:23
This query also returns an indeterminate result. It may 'work', but only by fluke!!! – Strawberry Jun 09 '15 at 16:01

Strawberry · Answer 2 · 2015-06-11T11:34:52.530

0

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 |
+------+-------+------+-------+

edited Jun 11 '15 at 11:34

answered Jun 08 '15 at 09:44

Strawberry

33,338
13
38
57

You should not be using `MAX(name)` anywhere. He is trying to get the row with the max stock, not the max name... – Blue Jun 08 '15 at 09:45
Join can not be on `parid` column, there is no such column in `stock` table – Shanil Soni Jun 08 '15 at 09:46
Nope, not giving proper result. Multiple entries are coming for a parid, whereas group by should only fetch one. – Shanil Soni Jun 08 '15 at 11:17
@Strawberry `Group by parid` should only fetch one row out of all the rows with same parid, your query fetches multiple rows. Example: `group by parid where parid=1` should fetch either `A` or `B`, not both of them. – Shanil Soni Jun 09 '15 at 03:41
With the two answers, I was able to come up with a query. Added to my question. – Shanil Soni Jun 09 '15 at 04:25
You're not using `group by parid` at the last so it's actually not grouping the rows. That's what the basic requirement is. I added a couple of elements to your query & that seems to work. – Shanil Soni Jun 09 '15 at 07:27
http://sqlfiddle.com/#!9/aad1b/1 is a fiddle where your query doesn't work. For products with same `parid`, I keep the stock same & for other zero. Your query produces unexpected results. – Shanil Soni Jun 09 '15 at 09:19
With the same stock value, there is no criteria. With different stock values, it should choose the one with highest stock. – Shanil Soni Jun 09 '15 at 14:37
Can you amend your fiddle (and desired result) to be more representative of this characteristic. – Strawberry Jun 09 '15 at 16:00
My requirement is: One product with highest stock value for each unique `parid` should be chosen. – Shanil Soni Jun 10 '15 at 03:47
And where highest stock values are tied WHICH ONE gets chosen? You cannot say 'it doesn't matter'. It SHOULD matter! – Strawberry Jun 10 '15 at 06:09
Just like how default works, it chooses in ascending order of `id` column. – Shanil Soni Jun 11 '15 at 05:09
So, in the event of a tie you want the one with the lowest id? – Strawberry Jun 11 '15 at 06:59

Group by in mysql with order by

2 Answers2