Getting the most recent record

[asmall]

I have been having a difficult time trying to get the most recent record for a client. Here is the code I am using.
The following code give me the results below.

SELECT
ad.authorization_details_id
,p.last_name + ', ' + p.first_name AS NAME
,av.event_name AS Training
,CAST(av.from_date AS DATE) AS StartDate
,CAST(av.to_date AS DATE) AS EndDate
,ad.units AS TotalUnits
,MAX(CAST(ad.date_entered AS DATE)) AS DateEntered
FROM
authorization_details ad
JOIN authorization_details_view av ON ad.authorization_details_id = av.authorization_details_id
JOIN people p ON av.people_id = p.people_id
WHERE p.last_name like 'DOE%' AND
GROUP BY
ad.authorization_details_id
,p.last_name + ', ' + p.first_name
,av.event_name
,ad.units
,av.from_date
,av.to_date
ORDER BY Name ,Training

--RESULTS--
NAME Training StartDate EndDate TotalUnits DateEntered
DOE, JOHN Management Training 2016-02-17 2017-02-16 96 2016-09-12
DOE, JOHN Management Training 2016-02-17 2017-02-16 48 2016-07-06

I get the results I want only when I remove the "TotalUnits" field.
However, when I add the "TotalUnits" field, I get every record and not the most recent.
I should only be getting one row.

NAME Training StartDate EndDate TotalUnits DateEntered
DOE, JOHN Management Training 2016-02-17 2017-02-16 96 2016-09-12

 

[Andrzejek]

I get the results I want only when I remove the "TotalUnits" field.

Get the Primary Key from the record you want, and ask for the data again with the PK and the "TotalUnits" field.


---- Andy

There is a great need for a sarcasm font.

 

[Olaf Doschke]

Well, you have two records, because their TotalUnits differ and you group by it. You surely don't want just the MIN, MAX, AVG or any other aggregate, but the TotalUnits on that max date_entered.
Don't group by it and get it later, just as Andy said already.

Olaf.

 

[TamarGranor]

If you're using SQL Server 2012 or later, you can do this with OVER and the LAST_VALUE() function. I wrote about it in this article:

http://www.tomorrowssolutionsllc.com/Articles/Using Over with Analytic Functions, Part 1.pdf

Tamar