×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Getting the most recent record

Getting the most recent record

Getting the most recent record

(OP)
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

RE: Getting the most recent record

Quote (asmall )

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.

RE: Getting the most recent record

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close