CODEWITHSUNDEEP
mysqlsqldatabase
Muhammad Hashir Anwaar
Muhammad Hashir Anwaar

Reputation: 614

Select latest history record from db

I have a table order_history which contain record of all actions that i perform on order like confirm order, attempted order, reject order or revert order.

When an employee mark order as attempt it create a history and after some time admin revert that order back to its original place (it also create history). After that, some other employee mark same order as attempt.

Problem

Now, history table has 2 records of same order for attempted. and i want to pick only latest attempted history because the previous action was reverted.

DB Structure

|history_id |order_id | date_added   |user_id | action_id|
|-----------|---------|--------------|--------|----------|
| 13        | 444     | 2018/07/06   |   9    |2         |
| 12        | 555     | 2018/07/05   |   7    |2         |
| 11        | 444     | 2018/07/05   |   2    |3         |
| 10        | 555     | 2018/07/05   |   2    |3         |
| 9         | 555     | 2018/07/05   |   4    |2         |
| 8         | 444     | 2018/07/04   |   1    |2         |

Where user_id = Employee and action_id 2 for attempt and 3 for revert back ,When order is attempted then revert and then again attempted by other employee, my query duplicate the order in both employee A and B but it should show in latest employee account.

MY Query

SELECT COUNT(oh.order_id) AS total_attempted,
       oh.user_id
FROM `order_history` oh 
WHERE oh.action_id = '2'
GROUP BY oh.user_id

Result

This query display order ID : 555 to both users user_id: 4 and 7 but order 555 should be only displayed for user 7.

Expected Output

|history_id |order_id | date_added   |user_id | action_id|
|-----------|---------|--------------|--------|----------|
| 13        | 444     | 2018/07/06   |   9    |2         |
| 12        | 555     | 2018/07/05   |   7    |2         |

PS: All action on order 555 was performed on same date

Lemme know if more details required.

Upvotes: 0

Views: 84

Answers (1)

P.Salmon
P.Salmon

Reputation: 17615

Your expected output does not tally with your attempted code. If you want only the latest attempt then you need to look at attempts AND reversions. 

drop table if exists oh;
create table oh (history_id int,order_id int,date_added varchar(100),user_id int,action_id int);
insert into oh (history_id ,order_id,date_added,user_id,action_id) values(13,444,"2018/07/06",9,2);
insert into oh (history_id ,order_id,date_added,user_id,action_id) values(12,555,"2018/07/05",7,2);
insert into oh (history_id ,order_id,date_added,user_id,action_id) values(11,444,"2018/07/05",2,3);
insert into oh (history_id ,order_id,date_added,user_id,action_id) values(10,555,"2018/07/05",2,3);
insert into oh (history_id ,order_id,date_added,user_id,action_id) values(9,555,"2018/07/05",4,2);
insert into oh (history_id ,order_id,date_added,user_id,action_id) values(8,444,"2018/07/04",1,2);
insert into oh values(7,333,"2018/07/04",1,3),(6,333,"2018/07/04",1,2),
(5,222,"2018/07/04",1,2),(4,222,"2018/07/04",2,2),
(3,111,"2018/07/04",1,2);

The sub query s finds the latest action based on history_id (I have assumed this indicates the order of events)

This code lists the latest attempts

select * from
(
select *
from   oh 
where  action_id in (2,3) and 
         history_id = (select max(history_id) from oh oh1 where oh1.order_id = oh.order_id)
) s
where s.action_id = 2;

+------------+----------+------------+---------+-----------+
| history_id | order_id | date_added | user_id | action_id |
+------------+----------+------------+---------+-----------+
|         13 |      444 | 2018/07/06 |       9 |         2 |
|         12 |      555 | 2018/07/05 |       7 |         2 |
|          5 |      222 | 2018/07/04 |       1 |         2 |
|          3 |      111 | 2018/07/04 |       1 |         2 |
+------------+----------+------------+---------+-----------+
4 rows in set (0.02 sec)

This code counts the number of attempts (excluding reversions by user)

select user_id,count(*) attempts
from
(
select *
from   oh 
where  action_id in (2,3) and 
         history_id = (select max(history_id) from oh oh1 where oh1.order_id = oh.order_id)
) s
where s.action_id = 2
group by user_id;

+---------+----------+
| user_id | attempts |
+---------+----------+
|       1 |        2 |
|       7 |        1 |
|       9 |        1 |
+---------+----------+
3 rows in set (0.00 sec)

Upvotes: 1

Related Questions