MySQL Forums
Forum List  »  Newbie

Re: Query based on selection criteria
Posted by: Peter Brawley
Date: January 14, 2020 10:28AM

Storing dates in the oddball US date format d/m/yyyy complicates and slows down date processing enormously, so you need to upgrade the table's date column to the internationally standard date format that MySQL uses, yyyy/mm/dd, eg ...

alter table tbl rename column datum_dt to old_dt;
alter table tbl add column datum_dt date;
update tbl set datum_dt=str_to_date(old_dt,'%d/%m/%Y');

Then your query is something like ...

-- rows where year >= current year-2
select account, datum_dt, address, city
from tbl
where left(datum_dt,4) >= year(curdate())-2

union

-- rows with max(date) from each account not in above group
select a.account, b.maxdt, a.address, a.city
from tbl a
join (
  select account, max(datum_dt) as maxdt
  from tbl
  group by account
) b on a.account=b.account and a.datum_dt=b.maxdt
where left(b.maxdt,4) < year(curdate())-2;

Options: ReplyQuote


Subject
Written By
Posted
Re: Query based on selection criteria
January 14, 2020 10:28AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.