-1

I know I could use min(date) to find the oldest date between a columns but how can I select the oldest date between 3 different columns?

Example:

Select date_1, date_2, date_3, do_something(oldest_date) from table;

EDIT: Actually my query is a little bit more complicated.

SELECT 
    (SELECT 
            IF(test.OLD = 1, DATE_FORMAT(test.DATE_EXPIRATION, '%d-%m-%Y'), IF(revalidation.recognized = 0, DATE_FORMAT(DATE_ADD(revalidation.DATE_test, INTERVAL revalidation.VALIDITY YEAR), '%d-%m-%Y'), IF(test.DATE_EXPIRATION = ''
                    OR test.DATE_EXPIRATION IS NULL, DATE_FORMAT(DATE_ADD(test.DATE_test, INTERVAL test.VALIDITY YEAR), '%d-%m-%Y'), DATE_FORMAT(test.DATE_EXPIRATION, '%d-%m-%Y')))) AS VENCIMIENTO
        FROM
            eva_compet revalidation
        LEFT JOIN eva_compet test ON (revalidation.recognized = test.EVA_LAB_ID)
        WHERE
            revalidation.TYPO_EVA_ID = 1
                AND revalidation.LABORAL_ID = cl.laboral_id
        LIMIT 1) AS DATE_1,
    (SELECT 
            IF(test.OLD = 1, DATE_FORMAT(test.DATE_EXPIRATION, '%d-%m-%Y'), IF(revalidation.recognized = 0, DATE_FORMAT(DATE_ADD(revalidation.DATE_test, INTERVAL revalidation.VALIDITY YEAR), '%d-%m-%Y'), IF(test.DATE_EXPIRATION = ''
                    OR test.DATE_EXPIRATION IS NULL, DATE_FORMAT(DATE_ADD(test.DATE_test, INTERVAL test.VALIDITY YEAR), '%d-%m-%Y'), DATE_FORMAT(test.DATE_EXPIRATION, '%d-%m-%Y')))) AS VENCIMIENTO
        FROM
            eva_compet revalidation
        LEFT JOIN eva_compet test ON (revalidation.recognized = test.EVA_LAB_ID)
        WHERE
            revalidation.TYPO_EVA_ID = 2
                AND revalidation.LABORAL_ID = cl.laboral_id
        LIMIT 1) AS DATE_2,
    (SELECT 
            IF(test.OLD = 1, DATE_FORMAT(test.DATE_EXPIRATION, '%d-%m-%Y'), IF(revalidation.recognized = 0, DATE_FORMAT(DATE_ADD(revalidation.DATE_test, INTERVAL revalidation.VALIDITY YEAR), '%d-%m-%Y'), IF(test.DATE_EXPIRATION = ''
                    OR test.DATE_EXPIRATION IS NULL, DATE_FORMAT(DATE_ADD(test.DATE_test, INTERVAL test.VALIDITY YEAR), '%d-%m-%Y'), DATE_FORMAT(test.DATE_EXPIRATION, '%d-%m-%Y')))) AS VENCIMIENTO
        FROM
            eva_compet revalidation
        LEFT JOIN eva_compet test ON (revalidation.recognized = test.EVA_LAB_ID)
        WHERE
            revalidation.TYPO_EVA_ID = 3
                AND revalidation.LABORAL_ID = cl.laboral_id
        LIMIT 1) AS DATE_3,
        CASE WHEN DATE_1 <= DATE_2 AND DATE_1 <= DATE_3 THEN DATE_1
            WHEN DATE_2 <= DATE_1 AND DATE_2 <= DATE_3 THEN DATE_2
            ELSE DATE_3
        END AS TERMINO
FROM
    table
WHERE
    table_id = 1001;

I tried to use the CASE as suggested but I am getting:

Error Code: 1054. Unknown column 'date_1' in 'field list'

SOLUTION (I followed the solution provided by @joe-stefanelli and the suggestion from @Uueerdo, THANKS ):

SELECT 
    DATE_1, 
    DATE_2, 
    DATE_3, 
    CASE WHEN DATE_1 <= DATE_2 AND DATE_1 <= DATE_3 THEN DATE_1
                WHEN DATE_2 <= DATE_1 AND DATE_2 <= DATE_3 THEN DATE_2
                ELSE DATE_3
            END AS OLDEST_DATE 
            FROM
(SELECT 
        (SELECT 
                IF(test.OLD = 1, DATE_FORMAT(test.DATE_EXPIRATION, '%d-%m-%Y'), IF(revalidation.recognized = 0, DATE_FORMAT(DATE_ADD(revalidation.DATE_test, INTERVAL revalidation.VALIDITY YEAR), '%d-%m-%Y'), IF(test.DATE_EXPIRATION = ''
                        OR test.DATE_EXPIRATION IS NULL, DATE_FORMAT(DATE_ADD(test.DATE_test, INTERVAL test.VALIDITY YEAR), '%d-%m-%Y'), DATE_FORMAT(test.DATE_EXPIRATION, '%d-%m-%Y')))) AS VENCIMIENTO
            FROM
                eva_compet revalidation
            LEFT JOIN eva_compet test ON (revalidation.recognized = test.EVA_LAB_ID)
            WHERE
                revalidation.TYPO_EVA_ID = 1
                    AND revalidation.LABORAL_ID = cl.laboral_id
            LIMIT 1) AS DATE_1,
        (SELECT 
                IF(test.OLD = 1, DATE_FORMAT(test.DATE_EXPIRATION, '%d-%m-%Y'), IF(revalidation.recognized = 0, DATE_FORMAT(DATE_ADD(revalidation.DATE_test, INTERVAL revalidation.VALIDITY YEAR), '%d-%m-%Y'), IF(test.DATE_EXPIRATION = ''
                        OR test.DATE_EXPIRATION IS NULL, DATE_FORMAT(DATE_ADD(test.DATE_test, INTERVAL test.VALIDITY YEAR), '%d-%m-%Y'), DATE_FORMAT(test.DATE_EXPIRATION, '%d-%m-%Y')))) AS VENCIMIENTO
            FROM
                eva_compet revalidation
            LEFT JOIN eva_compet test ON (revalidation.recognized = test.EVA_LAB_ID)
            WHERE
                revalidation.TYPO_EVA_ID = 2
                    AND revalidation.LABORAL_ID = cl.laboral_id
            LIMIT 1) AS DATE_2,
        (SELECT 
                IF(test.OLD = 1, DATE_FORMAT(test.DATE_EXPIRATION, '%d-%m-%Y'), IF(revalidation.recognized = 0, DATE_FORMAT(DATE_ADD(revalidation.DATE_test, INTERVAL revalidation.VALIDITY YEAR), '%d-%m-%Y'), IF(test.DATE_EXPIRATION = ''
                        OR test.DATE_EXPIRATION IS NULL, DATE_FORMAT(DATE_ADD(test.DATE_test, INTERVAL test.VALIDITY YEAR), '%d-%m-%Y'), DATE_FORMAT(test.DATE_EXPIRATION, '%d-%m-%Y')))) AS VENCIMIENTO
            FROM
                eva_compet revalidation
            LEFT JOIN eva_compet test ON (revalidation.recognized = test.EVA_LAB_ID)
            WHERE
                revalidation.TYPO_EVA_ID = 3
                    AND revalidation.LABORAL_ID = cl.laboral_id
            LIMIT 1) AS DATE_3,
            CASE WHEN DATE_1 <= DATE_2 AND DATE_1 <= DATE_3 THEN DATE_1
                WHEN DATE_2 <= DATE_1 AND DATE_2 <= DATE_3 THEN DATE_2
                ELSE DATE_3
            END AS TERMINO
    FROM
        table
    WHERE
        table_id = 1001)
        AS table_2;
Rodrick
  • 595
  • 10
  • 27
  • Possible duplicate of [What's the best way to select the minimum value from several columns?](https://stackoverflow.com/questions/368351/whats-the-best-way-to-select-the-minimum-value-from-several-columns) It even has the same answer. – Saaru Lindestøkke Dec 05 '18 at 21:59
  • I think [LEAST()](https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#function_least) might work for dates. – Uueerdo Dec 05 '18 at 22:03
  • This kind of problem can sometimes be symptomatic of poor design, so question whether yours is optimal – Strawberry Dec 05 '18 at 22:13

3 Answers3

1
SELECT CASE WHEN date_1 <= date_2 AND date_1 <= date_3 THEN date_1
            WHEN date_2 <= date_1 AND date_2 <= date_3 THEN date_2
            ELSE date_3
       END AS oldest_date
    FROM table;
Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
  • That makes a lot of sense. Probably is the right answer but I cannot use it on my query. I updated the question. – Rodrick Dec 05 '18 at 22:18
  • 1
    @Rodrick It should be perfectly fine for your query if you correct your query's syntax.You'll just need to move the case expression to a wrapping query. – Uueerdo Dec 05 '18 at 22:51
  • @Uueerdo What do you mean with "wrapping query"? On my edit I added the new query and I am getting - Unknown column 'date_1' - error. – Rodrick Dec 10 '18 at 14:36
  • 1
    @Rodrick You take your current query, and make it a subquery; like: SELECT date_1, date_2, date3, _this answer's case_ AS oldest_date FROM (_original query without case_) AS q – Uueerdo Dec 10 '18 at 19:32
  • @Uueerdo THANKS A LOT! – Rodrick Dec 11 '18 at 12:37
0

This answers the original post:

select date_1, date_2, date_3, do_something(a) from table, (select least(date_1, date_2, date_3) a from table) b;

I'm not going to try to figure out the complexities of the edit.

If all you want is the results of your function, then this:

select do_something(a) from (select least(date_1, date_2, date_3) a from table) b;

Arnold Cross
  • 199
  • 1
  • 12
0

Okay, I took a stab at it with the complexities. I'm not sure if this will work:

SELECT DATE_1, DATE_2, DATE_3, do_something(a) FROM b, ( SELECT LEAST(DATE_1, DATE_2, DATE_3) a FROM ( SELECT (...) as DATE_1, (...) as DATE_2, (...) as DATE_3 ) FROM table WHERE table_id = 1001 ) AS b ) AS c;

Arnold Cross
  • 199
  • 1
  • 12