Select the Last Value

Navigation
0

I have two entry and purchase tables, each relating to your detail: entry_interior and detail_buying , which I want to get the last cost of a product which will be delimited by the most recent date, either the entry or the purchase.

I have it solved partially using UNION but it throws me two results. I would like to know how I can select one nothing.

This is the query: 

            SELECT IFNULL(
                (SELECT  detalle_entrada.costo   FROM producto 

                INNER JOIN detalle_entrada ON detalle_entrada.idproducto = producto.idproducto
                INNER JOIN entrada ON entrada.identrada = detalle_entrada.identrada

                WHERE producto.idproducto = 153

                ORDER BY entrada.fecha_entrada DESC

                LIMIT 1),            
            0.0) as ultimo_costo

            UNION

            SELECT IFNULL(
                (SELECT compra_detalle.precio_unitario FROM producto 

                INNER JOIN compra_detalle ON compra_detalle.idproducto = producto.idproducto
                INNER JOIN compra ON compra.idcompra = compra.idcompra

                WHERE producto.idproducto = 153

                ORDER BY compra.fecha_compra DESC

                LIMIT 1),            
            0.0) as ultimo_costo

    
asked by Angel Colorado 08.02.2018 в 17:41
source

2 answers

1

You can add a 'limit' at the end of the sentence

Example: It gives you the first result 

select 'a' 
union 
select 'b' limit 1

Yes, you want only the second result use: 

limit 1,1
    
answered by 08.02.2018 / 17:53
source
0

Well I was doing several tests and thanks to the suggestion of @ Yasti I solved part of the problem and in the end that way I had the query: 

SELECT detalle_costo.costo FROM (

(SELECT compra_detalle.precio_unitario as costo, compra.fecha_compra as fecha FROM producto 

                        INNER JOIN compra_detalle ON compra_detalle.idproducto = producto.idproducto
                        INNER JOIN compra ON compra.idcompra = compra.idcompra

                        WHERE producto.idproducto = 153

                        ORDER BY compra.fecha_compra DESC

                        limit 1)

    UNION

(SELECT detalle_entrada.costo as costo, entrada.fecha_entrada as fecha FROM producto 

                        INNER JOIN detalle_entrada ON detalle_entrada.idproducto = producto.idproducto
                        INNER JOIN entrada ON entrada.identrada = detalle_entrada.identrada

                        WHERE producto.idproducto = 153

                        ORDER BY entrada.fecha_entrada DESC

                        limit 1)

    ) AS detalle_costo 

    ORDER BY detalle_costo.fecha DESC

    LIMIT 1

The result with LIMIT 1

And without the LIMIT 1

    
answered by 08.02.2018 в 20:34
UPDATE trigger mysql Memory to eclipse