SQLTeam.com | Weblogs | Forums

Retrieve row by latest date

#1

Hi all,

I've been all over the web and have tried several ways to complete my query but cannot quite find the answer. What I need to do is pull the date from the LastAccessed field, then pull the row for each VPUserName that has the latest LastAccessed date. Here is my code:

Select a.VPUserName, d.FirstName, d.LastName, Employee, LastAccessed from RPUP a

left join RPRT b on a.ReportID = b.ReportID
left join DDUP c on a.VPUserName=c.VPUserName
left join HRRM d on c.Employee = d.HRRef
Left Join (select max(convert(VARCHAR(10),LastAccessed,120)) from RPUP  Group by LastAccessed)  

GROUP BY  a.VPUserName, d.FirstName, d.LastName, Employee, LastAccessed
Order by a.VPUserName, LastAccessed DESC

Any suggestions?

#2

Are you you trying to find the latest value of LastAccessed column and then find all the records that have that value of LastAccessed? Or are you trying to find the LastAccessed row for each User? Assuming it is the former, which is what it sounds like from your posting, you could do this:

SELECT  a.VPUserName ,
        d.FirstName ,
        d.LastName ,
        Employee,
        l.MAXLastAccessed
FROM    RPUP a
        INNER JOIN ( SELECT  MAX(CONVERT(VARCHAR(10), x.LastAccessed, 120)) MAXLastAccessed
                    FROM    RPUP x
                  ) l ON l.MAXLastAccessed = a.LastAccessed

        LEFT JOIN RPRT b ON a.ReportID = b.ReportID
        LEFT JOIN DDUP c ON a.VPUserName = c.VPUserName
        LEFT JOIN HRRM d ON c.Employee = d.HRRef

I am writing this using minimal modifications to your existing query. But I would recommend some additional things - for example, get rid of the CONVERT in the inner select if LastAccessed column is of date/datetime data type.

If it is of DATETIME type, and if it has a time portion, and you want to get the rows that have the same date rather than the date and time, you will need to write the query slightly differently etc.

If this is not what you are looking for, post some sample input data with expected output.

#3

Thank you, James for your response. Unfortunately, this code didn't work and with some thought, we were able to approach the problem with another solution.

SELECT  VPUserName, MAX(CONVERT(VARCHAR(10), x.LastAccessed, 120)) MAXLastAccessed
                FROM    RPUP x
                    GROUP BY VPUserName
order by MAXLastAccessed Desc