Select 1 record of each ID based on the last date

Question

I have a fictitious table TB_META with the fields:

COD_VENDEDOR | DAT_ATIVACAO | VAL_META

Supposing I have these values in the table:

1 | 2011-01-01 | 1
1 | 2014-04-04 | 2
2 | 2012-01-01 | 3
2 | 2013-03-03 | 4
3 | 2013-01-01 | 5
3 | 2014-04-04 | 6

I want to get just one record of each ID. This record will be picked up based on the most current id date, generating this result

1 | 2014-04-04 | 2
2 | 2013-03-03 | 4
3 | 2014-04-04 | 6

My attempt was like this, but I don’t think I can enjoy any of this:

SELECT DISTINCT TQ.[COD_VENDEDOR]
                        ,TQ.[VAL_META]
                        ,TQ.[DAT_ATIVACAO]
                    FROM 
                        [dbo].[TB_META] TQ
                    INNER JOIN
                        [dbo].[TB_VENDEDOR] TV
                        ON TQ.COD_VENDEDOR = TV.COD_VENDEDOR
                    WHERE 
                        TQ.DAT_ATIVACAO = (SELECT TOP 1 DAT_ATIVACAO FROM TB_META WHERE COD_VENDEDOR = TQ.COD_VENDEDOR ORDER BY DAT_ATIVACAO)

Answer 1

Altering a little your subselect you easily reach the desired result:

SELECT TQ.[COD_VENDEDOR], TQ.[VAL_META], TQ.[DAT_ATIVACAO]
                FROM 
                    [dbo].[TB_META] TQ
                WHERE 
                    TQ.DAT_ATIVACAO = (SELECT MAX(DAT_ATIVACAO) 
                                       FROM TB_META 
                                       WHERE COD_VENDEDOR = TQ.COD_VENDEDOR 
                                       GROUP BY DAT_ATIVACAO)

I removed the table TB_VENDEDOR because it does not influence the answer, but if you want to replace the INNER JOIN, just edit the answer.

Answer 2

Following the logic you made, just sort it down like this: ORDER BY DAT_ATIVACAO DESC

Answer 3

I’m sorry but the code above is wrong:

The right thing would be:

SELECT DISTINCT(TQ.[COD_VENDEDOR]), TQ.[VAL_META], TQ.[DAT_ATIVACAO]
            FROM 
                [dbo].[TB_META] TQ
            WHERE 
                TQ.DAT_ATIVACAO = (SELECT MAX(DAT_ATIVACAO) 
                                   FROM TB_META 
                                   WHERE COD_VENDEDOR = TQ.COD_VENDEDOR 
                                   GROUP BY COD_VENDEDOR)