CODEWITHSUNDEEP
sqlsql-serverselect
sayth
sayth

Reputation: 7048

SQL aggregate using DISTINCT on ID by latest date

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

Upvotes: 2

Views: 180

Answers (3)

Laughing Vergil
Laughing Vergil

Reputation: 3756

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

Upvotes: 1

Adam
Adam

Reputation: 581

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.

Upvotes: 1

Mureinik
Mureinik

Reputation: 311188

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

Upvotes: 4

Related Questions