Click here to Skip to main content
15,944,733 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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.
Table name is Person.[Address]
Column names:
Person Number
Effective Date
Address Block

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

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.
 
Share this answer
 
Comments
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:
SELECT MAX([Effective Date] AS MaxAddressDate
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.
Assuming Microsoft SQL Server:
SQL
WITH SortedRecords As
(
    SELECT
        [Person Number],
        [Effective Date],
        [Address Block],
        ROW_NUMBER() OVER (PARTITION BY [Person Number] ORDER BY [Effective Date] DESC) As RN
    FROM
        [Person].[Details]
)
SELECT
    [Person Number],
    [Effective Date],
    [Address Block],
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[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900