2

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

Guy in the chair
  • 1,045
  • 1
  • 13
  • 42
  • 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. – Guy in the chair 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. – Guy in the chair 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. – Guy in the chair 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 – Guy in the chair 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. – Guy in the chair 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
  • Your solution (like Nikhil's) is indeterminate! – Strawberry Jun 09 '15 at 06:37

2 Answers2

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
Nikhil Batra
  • 3,118
  • 14
  • 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. – Guy in the chair 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. – Guy in the chair Jun 09 '15 at 03:51
  • With the two answers, I was able to come up with a query. Added to my question. – Guy in the chair 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
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 |
+------+-------+------+-------+
Strawberry
  • 33,750
  • 13
  • 40
  • 57