SQL select when dates are equal or give the max date

Question

0.00/5 (No votes)

See more:

Hi there,

I have the following dataset

ID_NO DateOfDeath PostDate
123 201501 201412
123 201501 201501
568 200601 201504
568 200601 201512

I need to select the ID_no when the DateOfDeath is equal to the PostDate. If it is not then it should select the ID_no with the maximum date.

The result should be:
ID_NO DateOfDeath PostDate
123 201501 201501
568 200601 201512

Posted 3-Feb-16 2:50am

Miss R

Add a Solution

Comments

ZurdoDev 3-Feb-16 9:17am

Probably just need a GROUP BY and possible a CASE statement in the WHERE clause. Where exactly are you stuck?

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)

Tomas Takac · Answer 1 · 2016-02-03T03:42:00

Solution 1

If we can assume that PostDate can never be greater than DateOfDeath then simple group by will do:

SQL

SELECT ID_NO, DateOfDeath, Max(PostDate) as PostDate FROM yourtable GROUP BY ID_NO, DateOfDeath

Posted 3-Feb-16 3:42am

Tomas Takac

Updated 3-Feb-16 3:45am

v3

Comments

Kornfeld Eliyahu Peter 3-Feb-16 9:48am

Based on the samples it is exactly the other way around...PostDate is never smaller than DateOfDeath...

Tomas Takac 3-Feb-16 13:23pm

Maybe it's my interpretation of dates but I see that always PostDate <= DateOfDeath (which kind of makes sense ;-)
ID_NO DateOfDeath PostDate
123 201501 > 201412
123 201501 = 201501
568 200601 > 201504
568 200601 > 201512

Kornfeld Eliyahu Peter 3-Feb-16 14:10pm

You are right...For some reason on my phone it split a way made it look the exact opposite order...Now on the big screen it is strait...

Richard Deeming · Answer 2 · 2016-02-03T05:35:00

Assuming Microsoft SQL Server, the ROW_NUMBER function[^] will do the trick:

SQL

WITH cteOrderedData As
(
    SELECT
        ID_NO,
        DateOfDeath,
        PostDate,
        ROW_NUMBER() OVER
        (
            PARTITION BY
                ID_NO
            ORDER BY
                CASE
                    WHEN DateOfDeath = PostDate THEN 0
                    ELSE 1
                END,
                PostDate DESC
        ) As RN
    FROM
        YourTable
)
SELECT
    ID_NO,
    DateOfDeath,
    PostDate
FROM
    cteOrderedData
WHERE
    RN = 1
;