MySQL Group by and Having clause issue

Question

I'm trying to get the correct records but for reason I've some issue in Having clause I guess, can anyone please help me out?

CASE 1: Trying to select rows where order_id = 1 (New Order) but should not have more than 1 record with the same order id

CASE 2: Select rows where order_id = 2 (Printed Order) but should also select new orders too and apply CASE 1, in other words Query should select where order_id=2 OR where order_id=1 if( order_id = 1 then should not have more than 1 record with the same order id)

I've a table where:
order_id = id of the order
status_id = different status id e.g 1 = New, 2 = Printed, 3 = Processing etc...
status_change_by = id of the admin who change the order status from new to printed to processing...

order_id | status_id | status_change_by
1        | 1         | (NULL)
1        | 2         | 12
1        | 3         | 12
2        | 1         | (NULL)
3        | 1         | (NULL)
4        | 1         | (NULL)
1        | 4         | 13
5        | 1         | (NULL)
3        | 2         | (NULL)

Here's my simple mySQL query:

SELECT * from order_tracking
where status_id = 1
group by order_id
having count(order_id) <= 2;

I even created SQL fiddle for the reference, please check if I'm doing wrong or I need complex query with CASE or IF statements?

http://sqlfiddle.com/#!2/16936/3

If this link doesn't work, please create one by this code:

CREATE TABLE order_tracking 
(
 track_id int auto_increment primary key, 
 order_id int (50), 
 status_id int(20), 
 status_changed_by varchar(30)
);

Here's the insertion:

INSERT INTO order_tracking
(order_id, status_id, status_changed_by)
VALUES
(1,1,''),
(1,2,12),
(1,3,12),
(2,1,''),
(3,1,''),
(4,1,''),
(1,4,13),
(5,1,''),
(3,2,'');

Your earliest response should be appreciated! Thanks for the time.

Desire result:

Case:1 which is quite simple where result should be something like that: Only New orders with no more than 1 record

Order_id | status_id | status_changed_by
2        |  1        | (NULL)
4        |  1        | (NULL)
3        |  1        | (NULL)

Case 2 result:

Order_id | status_id | status_changed_by
1        |  4(max id)| (NULL)
2        |  1        | (NULL)
4        |  1        | (NULL)
3        |  2(max id)| (NULL)

Please add your desired results for **both** cases to your post (use `edit` option). — PM 77-1, Oct 08 '14 at 22:14
Please look at you case descriptions and make sure that you use `order_id` vs. `status_id` appropriately. — PM 77-1, Oct 08 '14 at 22:29
What does your "*should not have more than 1 record with the same order id)*" mean? Do we select just one for the result or exclude such orders all together? — PM 77-1, Oct 08 '14 at 22:32

score 1 · Accepted Answer · answered Oct 09 '14 at 00:41

It seems, by reading between the lines of your question, that you want to display the highest numerical value of status for each order id. That is, it seems that your orders progress from status 1 to 2 to 3 and so forth.

Here's how you do that. First, you determine which is the highest status for each order, as follows:

    SELECT MAX(status_id) AS status_id, 
           order_id
      FROM order_tracking
     GROUP BY order_id

This query gives you one row for each order_id showing the maximum value of the status id.

Then, you use it as a subquery and join to your original table like so. http://sqlfiddle.com/#!2/16936/11/0

SELECT o.order_id, o.status_id, o.status_changed_by
  FROM order_tracking AS o
  JOIN (
        SELECT MAX(status_id) AS status_id, 
               order_id
          FROM order_tracking
         GROUP BY order_id
       ) AS m ON o.order_id = m.order_id AND o.status_id = m.status_id
 ORDER BY o.order_id

This will give you a nice result with the highest status for each order.

| ORDER_ID | STATUS_ID | STATUS_CHANGED_BY |
|----------|-----------|-------------------|
|        1 |         4 |                13 |
|        2 |         1 |                   |
|        3 |         2 |                   |
|        4 |         1 |                   |
|        5 |         1 |                   |

Please note: If you were to put an autoincrementing ID column into your order_tracking table, things might work better for you. You'd be able to get the most recently INSERTed status for each order_id rather than the numerically highest status. This would be a very helpful change to your table layout, in my opinion. You'd do that like this:

SELECT o.order_id, o.status_id, o.status_changed_by
  FROM order_tracking AS o
  JOIN (
        SELECT MAX(id) AS id, 
               order_id
          FROM order_tracking
         GROUP BY order_id
       ) AS m ON o.id = m.id
 ORDER BY o.order_id

Thanks @Ollie, you're life saver, also I'm already using auto_increment for tracking_id, again thx for your contribution and time. — Nadeem, Oct 09 '14 at 14:02

MySQL Group by and Having clause issue

1 Answers1