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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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