PHP SQL Has an issue

Question

This is my table, I should fetch the MAX (id) of each status_id.

id      status_id
10          1
11          1
12          2
13          2
14          2
15          4
16          4

So, I use this sql query, it works true and fetchs me all max ID.

select status_id, max(id) as max FROM `table` 
where status_id in (1,2,3,4) group by status_id

This sql command fetchs me 3 MAX id using while.

11, 14, 16....

You see, there is not any suitable id to 3rd status_id. And if there is not any suitable id to 3rd status_id just mark it as zero. So I want that sql will bring these results:

11, 14, 0, 16

I don't see status_id 3 in your table. so dont expect to get 0 — Drixson Oseña
– Drixson Oseña, Commented Sep 12, 2013 at 6:34
Yes, you dont see. And I want that if there is not status id 3 on my table, sql brings zero — user2767715
– user2767715, Commented Sep 12, 2013 at 6:35
Like this: The max id of 1st status_id is 11 The max id of 2nd status_id is 14 The max id of 3rd status id is 0 The max id of 4th status_id is 16 — user2767715
– user2767715, Commented Sep 12, 2013 at 6:37
@DipeshParmar, I did not try if null yet. Maybe it will help? — user2767715
– user2767715, Commented Sep 12, 2013 at 6:39

John Woo · Accepted Answer · 2013-09-12 06:41:41Z

4

You can create a subquery which basically has all the ID's you need and have a left join against it.

SELECT  a.status_ID,
        IFNULL(MAX(b.id), 0) maxVal
FROM    
        (
            SELECT 1 status_ID UNION ALL 
            SELECT 2 UNION ALL
            SELECT 3 UNION ALL
            SELECT 4 
        ) a
        LEFT JOIN `table` b ON a.status_id = b.status_id
GROUP   BY a.status_id

SQLFiddle Demo

edited Sep 12, 2013 at 6:41

answered Sep 12, 2013 at 6:36

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

knittl · Accepted Answer · 2013-09-12 06:56:46Z

0

You can join with a temporary dummy table containing all ids and a stats_id value of 0:

SELECT dummy.status_id, COALESCE(MAX(id), 0) AS max
FROM (
        SELECT 1 status_id
  UNION SELECT 2 status_id
  UNION SELECT 3 status_id
  UNION SELECT 4 status_id
) dummy
LEFT JOIN `table` 
ON dummy.status_id = table.status_id
GROUP BY dummy.status_id

But this does not scale and is a maintenance nightmare (you have to change the dummy-select in case a new status_id turns up). If you have a table containing ALL status_ids, replace the dummy-select with that table.

edited Sep 12, 2013 at 6:56

answered Sep 12, 2013 at 6:36

knittl

269k59 gold badges339 silver badges405 bronze badges

Collectives™ on Stack Overflow

PHP SQL Has an issue

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related