Mysql Query to find the last transaction date with the purchased item from Purchase_history table

Question

Table: purchase_history having all details of users

Fields are : id,uid, purchase_date, item_id, item_size, item_color

where id is a primary key.

There are many rows for an similar uid. e.g.

id | uid |  purchase_date | item_id | item_size | item_color 
1  | 200 |  2016-10-22    | 1021    |  3        | red 
2  | 122 |  2016-08-02    | 21      |  1        | black  
3  | 200 |  2016-05-01    | 222     |  1        | blue    
4  | 101 |  2016-01-07    | 102     |  1        | red

So now I want a single query to get the last transaction date, item_id and uid group by uid. I used below query:

select uid, max(purchase_date), item_id from purchase_history group by uid;

it gives me correct uid and purchase date but the item id is not picked from the last row. It is coming from the first row. Is there any way that we can find the item id from the last row with uid and purchase_date?

From the look of it, I think you want to order it first, then group it. Check [this post](http://stackoverflow.com/questions/14770671/mysql-order-by-before-group-by) — koceeng, Jan 10 '17 at 12:56

score 2 · Answer 1 · answered Jan 10 '17 at 12:48

You can find max of purchase date for each user in a subquery and join it with the main table like so:

select t1.uid, t1.purchase_date, t1.item_id
from purchase_history t1
inner join (
    select uid, max(purchase_date) purchase_date
    from purchase_history
    group by uid
) t2 on t1.uid = t2.uid
    and t1.purchase_date = t2.purchase_date;

NOTE: It'll give multiple rows for a uid, if there are rows with multiple max dates.

score 2 · Accepted Answer · answered Apr 23 '18 at 09:48

2

Try this:

select uid, max(purchase_date) as date, item_id from purchase_history group by uid ORDER by date desc,item_id desc

Make sure that you item_id type is an integer.

answered Apr 23 '18 at 09:48

Arti Singh

906
1
6
21

Thanks for the comment. – Sunil Kumar May 07 '18 at 12:38
@GurwinderSingh, I just checked your and Arti queries but the output is same, there is no difference. Is there anything that you want to explain? – Sunil Kumar May 08 '18 at 07:11
@Sunil - It may produce correct result now. But it is not reliable and may produce incorrect results later. Because the group expression is invalid as per ANSI syntax – Gurwinder Singh May 08 '18 at 08:39

score 0 · Answer 3 · answered Jan 10 '17 at 12:59

0

Use correlated subquery:

SELECT uid, purchase_date, item_id
FROM purchase_history p1
WHERE purchase_date = (
    SELECT MAX(purchase_date)
    FROM purchase_history p2
    WHERE p2.uid = p1.uid
);

answered Jan 10 '17 at 12:59

davidethell

11,708
6
43
63

score 0 · Answer 4 · answered Jan 10 '17 at 13:00

0

try this query

select * from (select * from purchase_history order by purchase_date asc) purchase_history group by uid;

answered Jan 10 '17 at 13:00

denny

2,084
2
15
19

Mysql Query to find the last transaction date with the purchased item from Purchase_history table

4 Answers4