How to filter by comparing the dates in MySQL Workbench

Question

I have a table look like this:

ID   B     C      date01        date02       date03
11  xxx   xxxx   2020-05-01    2020-05-02   2020-06-02
11  xxx   xxxx   2020-06-01    2020-06-03   2020-05-02
11  xxx   xxxx   2020-07-01    2020-07-03   2020-06-30
11  xxx   xxxx   2020-07-01    2020-06-03   2020-06-30
11  xxx   xxxx   2020-01-01    2020-01-08   2020-05-02
11  xxx   xxxx   2020-02-01    2020-01-31   2020-05-02
22  xxx   xxxx   2020-05-01    2020-05-02   2020-06-02
22  xxx   xxxx   2020-06-01    2020-06-03   2020-05-02
22  xxx   xxxx   2020-07-01    2020-07-03   2020-06-30
22  xxx   xxxx   2020-07-01    2020-06-03   2020-06-30
22  xxx   xxxx   2020-01-01    2020-01-08   2020-05-02
22  xxx   xxxx   2020-02-01    2020-01-31   2020-05-02

I want to return everything but with a latest date of those three dates for each ID, and date02 cannot be later than date03, my current output will give me this where date02 > date03:

11  xxx   xxxx   2020-07-01    2020-07-03   2020-06-30

Expected output:

11  xxx   xxxx   2020-07-01    2020-06-03   2020-06-30
22  xxx   xxxx   2020-07-01    2020-06-03   2020-06-30

I tried this:

SELECT 
    id,
    B,
    C,
    max(date01),
    max(date02), 
    max(date03),  
FROM 
    table
WHERE
    'date02' < 'date03'
GROUP BY id

I've added WHERE 'date02' < 'date03' but why the output still have the records where date02>date03?? I'm very new to SQL, please help...

GMB · Accepted Answer · 2020-05-04T13:59:53.590

1

You can do this with a correlated subquery and tuple equality:

select t.*
from mytable t
where (t.date01, t.date02, t.date03) = (
    select t1.date01, t1.date02, t1.date03 
    from mytable t1 
    where t1.id = t.id
    order by t1.date01 desc, t1.date02 desc, t1.date03 desc
    limit 1
)

For performance with this query, you can create a compound index on (id, date01, date02, date03).

You can also use row_number(), if you are running MySQL 8.0:

select *
from (
    select 
        t.*, 
        row_number() over(partition by id order by date01 desc, date02 desc, date03 desc) rn
    from mytable t
) t
where rn = 1

Demo on DB Fiddle:

ID | B   | C    | date01     | date02     | date03    
-: | :-- | :--- | :--------- | :--------- | :---------
11 | xxx | xxxx | 2020-07-01 | 2020-07-03 | 2020-06-30
22 | xxx | xxxx | 2020-07-01 | 2020-07-03 | 2020-06-30

edited May 04 '20 at 13:59

answered May 04 '20 at 13:34

GMB

216,147
25
84
135

Hi sorry about the missing info, I'm using workbench 6.3 CE, and a quick question about your answer ```select t1.date01, t1.date02, t1.date03```, by ```t1``` do you mean ```t```? – wawawa May 04 '20 at 13:43
I'm trying the first chunk of codes, and it shows 'running' for a very long time now, not sure what's happening. – wawawa May 04 '20 at 13:53
@Cecilia: If you have a large amount of data and you need to improve performance, you can create a compound index on `(id, date01, date02, date03)`. – GMB May 04 '20 at 13:58
@Cecilia: I do mean `t1` where it is written `t1`. Please run the query exacty as provided (you can also refer to the db fiddle that I added in my answer). – GMB May 04 '20 at 14:02
@Cecilia: 6.3 is your workbench version. To see your *mysql* version, you can run `select version()`. – GMB May 04 '20 at 14:02
@Cecilia: ok so obviously that's earlier than 8.0 - so you cannot use the second query, only the first one. Side note: MySQL 5.0 is a *really* old version, which is out of support - you should consider upgrading. – GMB May 04 '20 at 14:09
Hi thanks the reason I'm using the old version is the remote db is using an old version. Quick question about ```you can create a compound index on (id, date01, date02, date03)``` Can you be more specific please how to do this? – wawawa May 04 '20 at 14:18
@Cecilia: `create index myindex on mytable(id, date01, date02, date03)`... – GMB May 04 '20 at 14:19
@Cecilia: that's basic SQL syntax. You can google *how to create an index in MySQL*, or search the MySQL documentation. – GMB May 04 '20 at 14:26
Seems like it's been denied, it gave me an error ```Error Code: 1142. INDEX command denied to user 'xxxxxx'@'xxxxxxx' for table 'xxx'``` – wawawa May 04 '20 at 15:13

How to filter by comparing the dates in MySQL Workbench

1 Answers1