CODEWITHSUNDEEP
mysqlgroup-by
Sunil Kumar
Sunil Kumar

Reputation: 63

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

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?

Upvotes: 1

Views: 3227

Answers (4)

Arti Singh
Arti Singh

Reputation: 946

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.

Upvotes: 2

denny
denny

Reputation: 2264

try this query 

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

Upvotes: 0

davidethell
davidethell

Reputation: 12018

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
);

Upvotes: 0

Gurwinder Singh
Gurwinder Singh

Reputation: 39507

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.

Upvotes: 2

Related Questions