CODEWITHSUNDEEP
mysqlarraylist
iamP
iamP

Reputation: 317

return a unique list from query result after removing duplicate rows from the table

I have two columns product_id, r_store_id which have a few rows with same values. Rest of the column rows have different values I have duplicate rows with same r_store_id and product_id because every time I have to add new entries into this table. I want unique rows list with latest update_dt (refer the DB table below).

id | m_store_id |r_store_id|product_id | amount |update_dt |
1  |         4  |       1  |       45  | 10     |18/03/5   |
2  |         4  |       1  |       45  | 100    |18/03/9   |
3  |         4  |       1  |       45  | 20     |18/03/4   |
4  |         5  |       2  |       49  | 10     |18/03/8   |
5  |         5  |       2  |       49  | 60     |18/03/2   |
6  |         9  |       3  |       45  | 19     |18/03/5   |
7  |         9  |       3  |       45  | 56     |18/03/3   |

My result should look like this:

id | m_store_id |r_store_id|product_id | amount |update_dt |
2  |         7  |       1  |       45  | 100    |18/03/9   |
4  |         5  |       2  |       49  | 10     |18/03/8   |
6  |         9  |       3  |       45  | 19     |18/03/5   |

I want to put this result in a list like this:

List<Sales> salesList = (List<Sales>) query.list();

I am not able to find an easy solution. Please help me with this!

Upvotes: 0

Views: 22

Answers (1)

Pdubbs
Pdubbs

Reputation: 1987

We can select the chronologically most recent update for each store, and then join to get all the variables:

select a.*
from mytable a
join (select m_store_id, r_store_id, product_id, max(update_dt) as maxdate
      from mytable
      group by 1,2,3) b
on a.m_store_id=b.m_store_id
and a.r_store_id=b.r_store_id
and a.product_id=b.product_id
and a.update_dt = b.maxdate;

Upvotes: 1

Related Questions