CODEWITHSUNDEEP
mysqlsql-delete
Francesco G.
Francesco G.

Reputation: 717

mysql delete row that have a date greater than a specific date

I have a table like this

----------------------
| idDoc | date       | 
----------------------
| 1     | 2018-01-20 |
| 1     | 2018-07-15 |
| 1     | 2017-07-31 |
| 1     | 2019-01-17 |
| 1     | 2019-07-30 |
| 1     | 2020-01-11 |
| 1     | 2020-07-31 |
| 1     | 2021-01-20 |
| 15    | 2018-11-31 |
| 15    | 2019-03-17 |
| 15    | 2018-05-31 |
| 15    | 2017-05-29 |
| 15    | 2019-09-20 |
| 15    | 2020-12-31 |
| 5     | 2018-01-31 |
| 5     | 2017-07-31 |
| 5     | 2018-04-23 |
| 5     | 2019-11-31 |
| 5     | 2019-12-08 |
----------------------

and I would like that become (by single query) like this:

----------------------
| idDoc | date       | 
----------------------
| 1     | 2017-07-31 |
| 15    | 2017-05-29 |
| 5     | 2017-07-31 |
----------------------

The date to get will be always the older and the fields to delete will be always (with same id) all the date greater than it.

Any suggest?

Upvotes: 0

Views: 1627

Answers (4)

Rajendra
Rajendra

Reputation: 191

You can achieve this using join and date greater than, see this example for select : 

select * from docData d
LEFT JOIN docMaster m on d.idDoc=m.idDoc
where d.dateDoc>'2018-01-20';

i've example here : http://rextester.com/VAOB18193

Upvotes: 0

Blank
Blank

Reputation: 12378

If you want to delete data in db, try this:

delete t1
from demo t1
join demo t2
on t1.idDoc = t2.idDoc
and t1.`date` > t2.`date`;

See demo here.

If you want to select records like sample data, try this:

select t1.*
from demo t1
join (select idDoc, min(`date`) minDate from demo group by idDoc) t2
on t1.idDoc = t2.idDoc
and t1.`date` = t2.`minDate`;

Upvotes: 3

jamil
jamil

Reputation: 135

Its not totally clear what you want if you can specify more, but you can try this query 

select *
FROM mytable
WHERE 1 order by date asc group by idDoc

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

I would approach this as:

delete t
    from t join
         (select t2.idDoc, min(t2.date) as mindate
          from t t2
          group by t2.idDoc
         ) t2
         on t.idDoc = t2.idDoc
    where t.date > t2.mindate;

Upvotes: 1

Related Questions