SQL - get newest date from two different tables-CodePudding

I have 2 tables in my MySql database and I want to find one newest date from these two tables (one "newest" row from two tables). Some result of these tables can be null (sometimes only one select from one of tables can return something) How to do it best?

I have two tables: AAA with date_to and BBB with valid_to. I want to get the newest date from a.date_to and b.valid_to.

    WITH allDates AS (
    SELECT a.date_to
    FROM aaa a
             JOIN ramp r on a.ramp_id = r.id
             JOIN warehouse w on r.warehouse_id = r.warehouse_id
    WHERE w.id = 222
      AND a.date_from >= '2022-08-20T00:00'
      

    UNION

    SELECT b.valid_to as date_to
    FROM bbb b
    WHERE b.warehouse_id = 222
      AND b.status = 'ACTIVE'
)
select max(date_to) as dateTo
from allDates

With this query I have some sql error:

[42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'allDates AS ( SELECT a.date_to FROM aaa a JOIN ' at line 1

Additionally I want to use this script in my spring application (probably as native query in my JPA repository)

CodePudding user response：

Mysql doesn't support CTE, they where only introduced with Version 8.0 and higher.

As Long as you don't need a recursive CTE, you can move the CTE into the FROM clause

SELECT MAX(date_to) as dateTo
from (
        SELECT a.date_to
        FROM aaa a
                 JOIN ramp r on a.ramp_id = r.id
                 JOIN warehouse w on r.warehouse_id = r.warehouse_id
        WHERE w.id = 222
          AND a.date_from >= '2022-08-20T00:00'      
        UNION
        SELECT b.valid_to as date_to
        FROM bbb b
        WHERE b.warehouse_id = 222
          AND b.status = 'ACTIVE'
    ) allDates