CODEWITHSUNDEEP
sqlsql-servert-sql
Welldonebacon
Welldonebacon

Reputation: 83

Only select most recent record (calldatetime) SQL

Im trying to only select the most recent calldatetime and display that in my results, can anyone help? I need the results to only show the latest updated record

SELECT
    ROW_NUMBER() OVER (PARTITION BY l.phonenum ORDER BY h.calldatetime) as rn,
    h.HistoryID,
    l.ProjectID,
    h.ProjName,
    l.CompanyName,
    l.Phonenum,
    l.fname,
    l.lname,
    l.Address1,
    l.Address2,
    l.Address3,
    l.Town,
    l.Postcode,
    l.county,
    h.CallDateTime,
    cb.CBDatetime,
    l.apptdate,
    l.appttime,
    h.CRC,
    c.Description,
    a.Firstname + ' ' + a.Lastname AS AgentName,
    l.Notes
FROM
    History h inner join
    cmp_UtilityTrade l on h.PhoneNum = l.Phonenum left outer join
    CRC c on c.CRC = h.CRC left outer join
    Agent a on a.AgentID = h.AgentID left outer join
    CallBack cb on cb.DialID = h.DialID
WHERE
    h.calldatetime BETWEEN '2016-05-01 00:00:00' AND '2016-06-15 23:00:00'
ORDER BY
    l.Phonenum

Upvotes: 0

Views: 59

Answers (1)

sstan
sstan

Reputation: 36513

It looks like you already have the row_number() over window function setup the way you need it. Assuming it's returning the correct value, you simply need to filter your results by that rn value. Just place your existing query in a CTE, and filter the results like so:

with cte as (<your_query_without_order_by_phonenum>)
select *
  from cte
 where rn = 1
 order by phonenum

Also note that you probably want to move the order by l.Phonenum clause outside the CTE, and place it in the outer query.

EDIT

As noted in the comments, you may also have to fix the order by clause inside the row_number() window function to h.calldatetime DESC.

Upvotes: 1

Related Questions