SQL aggregate using DISTINCT on ID by latest date

Question

Request I have a section of data below and my goal is to limit the agent column to be distinct only containing unique values, where the unique value selected is the latest date it was modified.

Existing Data

modified     agent  rank 
2016-10-18  346502  0   
2013-06-04  346502  41  
2011-10-31  346503  0   
2012-08-13  346505  0   
2016-04-18  346506  66  
2015-01-27  346506  1   
2016-01-21  346507  103 
2015-01-27  346507  130 
2012-01-30  346508  0

Trying to use this answer https://stackoverflow.com/a/29912858/461887 as a basis but cannot get where to aggregate it properly.

SQL not working

SELECT DISTINCT
  FLiex.agtprof.modify_date_time
  ,FLiex.agtprof.agent_id
  ,FLiex.agtprof.rank
  ,FLiex.agtprof.external_id
WHERE
    FLiex.agtprof.modify_date_time = MAX( FLiex.agtprof.modify_date_time)
FROM
  FLiex.agtprof

Desired Output

modify  agent   rank
18/10/2016  346502  0
18/04/2016  346506  66
21/01/2016  346507  103
13/08/2012  346505  0
30/01/2012  346508  0
31/10/2011  346503  0

score 4 · Accepted Answer · answered Dec 20 '16 at 22:40

4

You're attempting to get single row data, but based on the other rows. While this may be possible with aggregate functions, it's much easier to do with window (analytic) functions:

SELECT [modified], [agent], [rank], [id]
FROM   (SELECT [modified], [agent], [rank], [id],
               ROW_NUMBER() OVER (PARTITION BY [agent]
                                  ORDER BY [modified] DESC) AS rn
        FROM   [agtprof]) t
WHERE  rn = 1

answered Dec 20 '16 at 22:40

Mureinik

297,002
52
306
350

This looks the best solution however I could not get the second From [table]) t to work. It returns invalid object name. I have voted up yours as I need to learn more about it and also voted up the one from @Laughing Vergil I got working – sayth Dec 21 '16 at 02:40
SELECT [FLiex.agtprof.modify_date_time], [,FLiex.agtprof.agent_id], [,FLiex.agtprof.rank], [,FLiex.agtprof.external_id] FROM (SELECT [FLiex.agtprof.modify_date_time], [,FLiex.agtprof.agent_id], [,FLiex.agtprof.rank], [,FLiex.agtprof.external_id], ROW_NUMBER() OVER (PARTITION BY [,FLiex.agtprof.agent_id] ORDER BY [FLiex.agtprof.modify_date_time] DESC) AS rn FROM [FLiex.agtprof]) t WHERE rn = 1 – sayth Dec 21 '16 at 02:42

score 1 · Answer 2 · answered Dec 20 '16 at 22:42

1

SELECT DISTINCT max(id_date), agent, rank, id
FROM fliex.agtprof
GROUP BY 2,3,4;

Try this. I think if you chose the max id_date and then group by the rest, you should get the results you're looking for.

answered Dec 20 '16 at 22:42

Adam

581
2
4
12

score 1 · Answer 3 · answered Dec 20 '16 at 22:43

Try this:

SELECT 
    FLiex.agtprof.modify_date_time
    ,FLiex.agtprof.agent_id
    ,FLiex.agtprof.rank
    ,FLiex.agtprof.external_id
FROM
    FLiex.agtprof
INNER JOIN (
    SELECT 
        Max(FLiex.agtprof.modify_date_time) as max_mod_date_time
        ,FLiex.agtprof.agent_id as agent_id
    FROM
        FLiex.agtprof
    GROUP BY FLiex.agtprof.agent_id
    ) Filter
    ON FLiex.agtprof.agentID = Filter.agent_id
    AND FLiex.agtprof.modify_date_time = Filter.max_mod_date_time

SQL aggregate using DISTINCT on ID by latest date

3 Answers3