How to take the variable with the oldest date - SQL SP

Question

How to take the variable with the oldest date - SQL SP

Navigation

#1 by (1 votes)

1

I have a stored procedure, which must take from a set of variables, the one that contains the oldest date in the field DATE_CREATION

a product code is in different tables, I must take the data from the table with the oldest date and pass it to the variable DATE_MAS_ANTIGUA.

first set the date in a variable:

SET FECHA_CREACION_tb1   = (SELECT FECHA_CREACION FROM PRODUCTO_tb1  WHERE CODIGO_PRO = codigo_new ) ;
SET FECHA_CREACION_tb2   = (SELECT FECHA_CREACION FROM PRODUCTO_tb2   WHERE CODIGO_PRO = codigo_new ) ;
SET FECHA_CREACION_tb3   = (SELECT FECHA_CREACION FROM PRODUCTO_tb3   WHERE CODIGO_PRO = codigo_new ) ;

up there all right,

but then I need to take the variable with the highest date, and that's where I do not succeed,

What I am currently doing is:

SET FECHA_MAS_ANTIGUA = MIN(FECHA_CREACION_tb1, FECHA_CREACION_tb2, FECHA_CREACION_tb3);

Someone knows how I can take the oldest date data from a set of variables and pass it to a variable

UPDATE (SOLUTION):

SET  FECHA_MAS_ANTIGUA = (SELECT MIN(T.FECREA_PRO)
FROM (SELECT FECREA_PRO FROM TB1  WHERE CODIGO_PRO = codigo_new
    UNION
    SELECT FECREA_PRO FROM TB2  WHERE CODIGO_PRO = codigo_new
    UNION
    SELECT FECREA_PRO FROM TB3  WHERE CODIGO_PRO = codigo_new
    UNION
    SELECT FECREA_PRO FROM CTB4  WHERE CODIGO_PRO = codigo_new
    UNION
    SELECT FECREA_PRO FROM TB5 WHERE CODIGO_PRO = codigo_new
) T);

sql stored-procedure

asked by Francisco Acevedo 19.06.2018 в 17:33

source

1 answer

how to insert data in php array, mysqli? Error with default values

score 1 · Accepted Answer

The simplest way could be using a subquery joining the three queries you have in the following way:

SET FECHA_MAS_ANTIGUA = (SELECT MIN(T.FECHA_CREACION)
    FROM (SELECT FECHA_CREACION FROM PRODUCTO_tb1  WHERE CODIGO_PRO = codigo_new
        UNION
        SELECT FECHA_CREACION FROM PRODUCTO_tb2  WHERE CODIGO_PRO = codigo_new
        UNION
        SELECT FECHA_CREACION FROM PRODUCTO_tb3  WHERE CODIGO_PRO = codigo_new
    ) T);