Can this query be improved/optimized ? Join query with 2 tables

Steffy Alen

3 years ago

I got 2 tables like such:

Record table:

code	date
0001	2020-12-31
0001	2020-12-30
0002	2021-01-04
0002	2020-12-31

Valid_date table, the dates here ain’t linear:

date
2021-01-06
2021-01-05
2021-01-04
2020-12-31
2020-12-30
2020-12-29

The result I want is the next valid date of each code:

code	next_date
0001	2021-01-04
0002	2021-01-05

My current take is like this, using sub query. I got the result that I need but it seems not the best way. Any hints for better syntax ?

SELECT sub.code,
       Min(sub.date)
FROM   (SELECT jt.code,
               rc.date
        FROM   record rc,
               (SELECT code,
                       Max(date) mdate
                FROM   record
                GROUP  BY code) AS jt
        WHERE  rc.date > jt.mdate
        ORDER  BY jt.code) AS sub
GROUP  BY sub.code