How to do select picking the record that has the maximum value of a field?

Navigation
1

I'm doing a select that is looking for records that meet certain conditions and then you should get only the record that has the maximum value of a given field.

You're 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'm looking for the period: CGMP.NUM_PERIODO_LANCAMENTO = 4 but I do not want to specify the period because the maximum period can be longer than that. I wanted the query to take the record with maximum period value, but only after fulfilling the other "where".

    
asked by anonymous 31.07.2015 / 15:28

1 answer

2

One of the solutions is to make a subquery to get the MAX of this 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
    )
    
31.07.2015 / 15:34
Jquery Scroll Smoth How to use the upload approach saving the path in the bank?