How to mount SELECT?

Asked

Viewed 280 times

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 assemble a SELECT that brings the products with the current price, ie the most recent price of the History table, but so far I could not do.

Can someone help me?

sql select

  • You want to mount SQL via C# (Linq) or via sql code to run in the database?

  • SQL code to run in the database

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)

4

The consultation proposed by the Roma follows an alternative.

The query below will not work on MySQL, but is valid in 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

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

        )

Browser other questions tagged sql select

You are not signed in. Login or sign up in order to post.