Select 1 record of each ID based on the last date

Question

Select 1 record of each ID based on the last date

Navigation

#1 by (5 votes)
#2 by (0 votes)
#3 by (0 votes)

4

I have a dummy TB_META table with the fields:

COD_VENDEDOR | DAT_ATIVACAO | VAL_META

Assuming 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 only one record of each ID. This record will be taken based on the most current date of the id, 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 do not think I can take advantage of 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)

sql sql-server

asked by anonymous 23.07.2014 / 05:01

3 answers

Format date in PHP [duplicate] git push command with no apparent effect

score 5 · Answer 1

By changing your subselect a little, 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 
                                       ORDER BY DAT_ATIVACAO)

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

score 0 · Answer 2

0

Following the logic you did, just sort of decrement like this: ORDER BY DAT_ATIVACAO DESC

23.07.2014 / 08:39

score 0 · Answer 3

Excuse me but the above code is wrong:

The correct one 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)