15,944,733 members
See more:
Hi
I have a table that contains person number, effective date and address.

Many person numbers have multiple address records. I need to select only the record that contains the max effective date for the address for each person number.
Column names:
Person Number
Effective Date

Many thanks

What I have tried:

I'm new to sql so haven't tried anything of substance and am struggling to understand solutions when in a different context. Thanks
Posted
Updated 1-Aug-22 23:10pm

## Solution 1

Look at using a GROUP BY clause: SQL GROUP BY Statement[^] - that can return the Person number and the MAX Effective Date, which you can then JOIN with the original table to get the whole row for each person.

Elldubzz 31-Jul-22 2:59am
As I am totallY hopeless at sql please could you help me with the actual code. I've tried the following but there is an issue with the syntax:
FROM [Person].[Details]
GROUP BY [Person Number]
ORDER BY [Person Number] DESC

OR

SELECT MAX (Effective Date) AS 'Max Date'
FROM Person.[Details]
GROUP BY [Person Number]
ORDER BY [Person Number] DESC
OriginalGriff 31-Jul-22 3:16am
Stop guessing, and think logically.
Use SSMS to test your queries and see that you get what you expect, and build your query a bit at a time, test it, and then move on to use those results.

So what did you get from the GROUP BY? Remember I can't access your DB or see your screen!
Elldubzz 31-Jul-22 3:01am
also, a correction, the table name is Person.Details not Person.Address.

## Solution 2

Assuming Microsoft SQL Server:
SQL
```WITH SortedRecords As
(
SELECT
[Person Number],
[Effective Date],
ROW_NUMBER() OVER (PARTITION BY [Person Number] ORDER BY [Effective Date] DESC) As RN
FROM
[Person].[Details]
)
SELECT
[Person Number],
[Effective Date],
FROM
SortedRecords
WHERE
RN = 1
ORDER BY
[Person Number] DESC
;```
NB: If you can, I'd recommend renaming your columns so that they don't contain spaces. As you can see, when the column name contains "special" characters, you have to wrap it in square brackets.
Database Identifiers - SQL Server | Microsoft Docs[^]