How to mount SELECT?

Navigation
6

I have a Products table with: 

ProCodigo - Primary Key de Produto
ProNome

And another Price History table with: 

HisCodigo - Primary Key de Histórico
HisData
HisPreco
ProCodigo - Foreign Key de Produto

I need to mount a SELECT that brings the products with the current price, that is the most recent price in the History table, but so far I have not been able to do it.

Can anyone help me?

    
asked by anonymous 22.01.2016 / 18:06

3 answers

9
SELECT P.PROCODIGO, P.PRONOME, H.HISPRECO
FROM PRODUTO P
INNER JOIN HISTORICO H ON P.PROCODIGO = H.PROCODIGO
WHERE H.HISDATA = (SELECT MAX(HISDATA) FROM HISTORICO WHERE PROCODIGO = P.PROCODIGO)
    
22.01.2016 / 18:33
4

There is an alternative to the query proposed by the Gypsy.

The query below will not work on MySQL , but is valid on PostgreSQL , Oracle and SQL Server . 

WITH CTE_PRODUTO AS (
    SELECT 
        ROW_NUMBER() OVER (PARTITION BY P.PROCODIGO ORDER BY H.HISDATA DESC) INDICE,
        P.PROCODIGO, 
        P.PRONOME, 
        H.HISPRECO
    FROM PRODUTO P
    INNER JOIN HISTORICO H ON P.PROCODIGO = H.PROCODIGO
)

SELECT PROCODIGO, PRONOME, HISPRECO 
FROM CTE_PRODUTO 
WHERE INDICE = 1
    
22.01.2016 / 19:03
1

Try this: 

        SELECT
        PRONOME
        FROM
        PRODUTOS P
        INNER JOIN
        HISTORICO H
        ON (P.PROCODIGO = H.PROCODIGO)
        WHERE
        H.HISDATA = (
            SELECT
            MAX (HISDATA)
            FROM
            HISTORICO H2
            ON
            H.PROCODIGO = H2.PROCODIGO

        )
    
22.01.2016 / 18:35
How can I generate a 7-character alphanumeric key? Count how many pixels of a color the image has