My table is something like this:
Name Order Goods Date
Michael 1 Shoes 2019/04/05
Michael 2 Groceries 2019/05/28
Michael 3 Caps 2019/03/02
Lucas 4 Shoes 2019/02/30
Lucas 5 Caps 2019/03/31
Mary 6 Shoes 2018/04/22
Mary 7 Shoes 2018/03/25
Mary 8 Groceries 2017/08/22
Mary 9 Caps 2019/01/01
How to define a Query so that I can delete rows obbeying the following conditions?
First and Foremost I want to group everything by the Name collumn;
"Shoes" is my reference. I need to check If any customer has bought "Shoes" and will keep any other Goods registered if and only if the buying date is earlier than the "Shoes" buying date of that customer (grouped by the Name collumn)(i.e if any good other than Shoes has been bought after the Shoes buying date, this any other good row will be deleted).
I keep only the first buying date of Shoes to compare. Newer dates are deleted too. Only the first one (older one) is kept.
So, I will have a table like below:
Michael 1 Shoes 2019/04/05
Michael 3 Caps 2019/03/02
Lucas 4 Shoes 2019/02/30
Mary 7 Shoes 2018/03/25
Mary 8 Groceries 2017/08/22
Thank you
You can join an aggregate query that computes the first date when each customer ordered shoes, and use that information to filter the rows to delete:
delete t
from mytable t
inner join (
select name, min(date) min_date
from mytable
where goods = 'Shoes'
group by name
) t1 on t.name = t1.name and t.date > t1.min_date
Name | OrderID | Goods | Date :------ | ------: | :-------- | :--------- Michael | 1 | Shoes | 2019-04-05 Michael | 3 | Caps | 2019-03-02 Lucas | 4 | Shoes | 2019-02-28 Mary | 7 | Shoes | 2018-03-25 Mary | 8 | Groceries | 2017-08-22
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments