How to select by taking the record that has the maximum value of a field?

Question

I am doing a select that is seeking records that meet some conditions and then should take from them only the record that has the maximum value of a given field.

It’s like this:

SELECT 
    VP.VAL_PREVISTO
FROM 
    TB_META M 
INNER JOIN 
    TB_SUPER_FUNCAO_META SFM ON 
    M.COD_META = SFM.COD_META 
    AND SFM.COD_FUNCAO = (SELECT F.COD_SUPER_FUNCAO U FROM TB_USUARIO U INNER JOIN TB_FUNCAO F ON U.COD_FUNCAO = F.COD_FUNCAO WHERE U.COD_USUARIO = 5)
INNER JOIN
    TB_VALOR_PREVISTO VP ON
    M.COD_META = VP.COD_META
INNER JOIN
    TB_CG_META_PERIODO CGMP ON
    VP.COD_VALOR_PREVISTO = CGMP.COD_VALOR_PREVISTO       
WHERE
    SFM.IND_EXIBIR_MC_CG = 1 AND M.COD_META = 44 AND CGMP.NUM_PERIODO_LANCAMENTO = 4

In this query I am searching the period: CGMP.NUM_PERIODO_LANCAMENTO = 4 but I don’t want to specify the period, because the maximum period may be longer than that. I wanted the query to get the record with maximum period, but only after the other "Where".

Answer 1

One of the solutions is to make a subquery to fetch the MAX of that element:

SELECT 
    VP.VAL_PREVISTO
FROM 
    TB_META M 
INNER JOIN 
    TB_SUPER_FUNCAO_META SFM ON 
    M.COD_META = SFM.COD_META 
    AND SFM.COD_FUNCAO = (SELECT F.COD_SUPER_FUNCAO U FROM TB_USUARIO U INNER JOIN TB_FUNCAO F ON U.COD_FUNCAO = F.COD_FUNCAO WHERE U.COD_USUARIO = 5)
INNER JOIN
    TB_VALOR_PREVISTO VP ON
    M.COD_META = VP.COD_META
INNER JOIN
    TB_CG_META_PERIODO CGMP ON
    VP.COD_VALOR_PREVISTO = CGMP.COD_VALOR_PREVISTO       
WHERE
    SFM.IND_EXIBIR_MC_CG = 1 AND M.COD_META = 44 
    AND CGMP.NUM_PERIODO_LANCAMENTO = (
        SELECT MAX(C.NUM_PERIODO_LANCAMENTO)
        FROM TB_CG_META_PERIODO C
    )