Originally Posted by
Russellh
If I have a table with the columns
LOCATION #...Customer #....Customer NAME...CONNECTIONDATE
And the same location # has several records (1 record for each person who has lived there).
How do I pull 1 record for each location and that record be the one with the most recent connectiondate.
All Dates are in the format YYMMDD so i should just be able to use the highest number. For instance May 23, 2106 would be 160523.
I see there is a last function or min/max function but I'm not sure how to use it here to pull all the location #s but the most recent customer for each location.
I'm freehanding this, so there may be typos:
Code:
SELECT TABLE.* FROM TABLE, (SELECT FILTER.LOCATION, MAX(FILTER.CONNECTIONDATE) MAX_CONNECTIONDATE FROM TABLE AS FILTER) AS FILTER_CONNECTION WHERE TABLE.LOCATION = FILTER_CONNECTION.LOCATION AND TABLE.CONNECTIONDATE = FILTER_CONNECTION.MAX_CONNECTIONDATE;