Get distinct name, address, max(date) while preserving ID

I have the following table structure:

ID | fname | lname | street | date

I'm trying to grab the distinct fname, lname, street and max(date) but also preserve the id of the matching row. So there might be multiple lines of matching fname, lname, street but all with different IDs Seems like a simple thing but evidently it's escaped me to this point.

I found some solutions that almost fit this but not quite. My apologies if this has been covered.

Thanks.

Answers


Try the following:

;WITH CTE AS
(
    SELECT *, ROW_NUMBER() OVER(PARTITION BY fname, lname, street ORDER BY [Date] DESC) RN
    FROM yourTable
)
SELECT ID, fname, lname, street, [date]
FROM CTE
WHERE RN = 1

Assuming max(date) is in the max(id):

select max(ID), fname, lname, street, max(date)
from tablename
group by fname, lname, street

Need Your Help

fullcalendar "typeError: Cannot read property 'hasTime' of undefined"

javascript jquery plugins fullcalendar

My jQuery Full Calendar was working fine until I added some events. I am now getting the error: fullcalendar "typeError: Cannot read property 'hasTime' of undefined" I think this is referring to r...