HELP -find the record with the max effective date for each person number

Question

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 30-Jul-22 20:30pm

Elldubzz

Updated 1-Aug-22 23:10pm

Add a Solution

2 solutions

Add a Solution

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

OriginalGriff · Answer 1 · 2022-07-30T20:49:00

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.

Posted 30-Jul-22 20:49pm

OriginalGriff

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.

Richard Deeming · Answer 2 · 2022-08-01T23:10:00

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[^]

HELP -find the record with the max effective date for each person number

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

HELP -find the record with the max effective date for each person number

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us