MySQL : How to get latest row on optional where clause using group by?

Question

We have one table- Product_table:

product_id | company_id | Status | must_show
1          | 23         | 1      | 1
2          | 23         | 1      | 1
3          | 23         | 1      | 0
4          | 23         | 1      | 0
5          | 23         | 0      | 0
6          | 24         | 1      | 0
7          | 24         | 1      | 0
8          | 24         | 1      | 0
9          | 24         | 1      | 0
10         | 24         | 0      | 0

We need to find the max product_id of company where status is 1. For that we are using below query:

select * from Product_table as pt
JOIN (select MAX(product_id) as extid from Product_table) t1 ON t1.extid = 
pt.product_id where company_id in (23,24) and status = 1 group by company_id;

Result:

product_id| company_id| Status | must_show
4         | 23        | 1      | 0
9         | 24        | 0      | 0

This query is fine but we have an issue here.

If value for must_show is 1 then we need show max_product id for company with must_show=1 and status=1.
If value for must_show is 0 then we need show max_product id for company with status=1.

Expected Result:

product_id| company_id| Status | must_show
    2     | 23        | 1      | 1
    9     | 24        | 1      | 0

Please give me the path to find a solution. Thanks!

For `product_id=10, status is 0` then why you want to show that? You have mentioned that - `We need to find the max product_id of company where status is 1.` Then why record with product_id=10 is there in expected result ? — Keyur Panchal, Jul 12 '17 at 13:17
Yes, when there is 1 in must_show then we must need to show that record. Actually changing the priority. — Shubhneet Srivastava, Jul 12 '17 at 13:25
I am talking about `product_id=10`. For that record `status` and `must_show` both are 0. — Keyur Panchal, Jul 12 '17 at 13:26
Now again, for `product_id=9`, status=1 in actual table. Please relax before confirming your edit. — Keyur Panchal, Jul 12 '17 at 13:30
`GROUP BY` doesn't help on this kind of problem. Check [this answer](https://stackoverflow.com/a/28090544/4265352) on a similar question. — axiac, Jul 12 '17 at 14:13

Keyur Panchal · Accepted Answer · 2017-07-12T14:09:11.873

Please try below query:

select max(t.product_id) product_id, t.company_id, t.status, t.must_show from
( select p1.product_id, p1.company_id, p1.status, p1.must_show
   from product_table p1
    where status = 1
     and must_show = (
      select max(must_show) from product_table p2 where p2.company_id = p1.company_id)
) as t group by t.company_id;

Output:

+------------+------------+--------+-----------+
| product_id | company_id | status | must_show |
+------------+------------+--------+-----------+
|          2 |         23 |      1 |         1 |
|          9 |         24 |      1 |         0 |
+------------+------------+--------+-----------+

company_id in (23,24)- I think you don't need this condition as there are only these two company_id present in your table as per your question data.

Thanks! I got it. This may reduce the speed of the page but working. — Shubhneet Srivastava, Jul 12 '17 at 14:21

MySQL : How to get latest row on optional where clause using group by?

1 Answers1