How to get the latest record against particular id in SQL

Question

0.00/5 (No votes)

See more:

InspNo					PartnerCode	CreaOn					IsPush	PushToPartner
I/2023/400018/00097602	MFC			2023-11-01 20:25:19.477	NULL	NULL
I/2023/400018/00097602	MFC			2023-11-01 20:06:18.480	NULL	NULL
I/2023/400018/00097601	ADR			2023-11-01 19:41:50.970	NULL	NULL
I/2023/400018/00097601	MFC			2023-11-01 19:33:18.303	NULL	NULL
I/2023/400018/00097601	ADR			2023-11-01 14:30:04.463	NULL	NULL
I/2023/400018/00097600	ADR			2023-10-31 16:29:09.950	NULL	NULL
I/2023/400015/00085384	ADR			2023-10-31 16:08:40.390	NULL	NULL

Above is my table.
What I want is the latest record inspection wise on the basis of partner code.

Excepted output
I want only 1 record Latest of I/2023/400018/00097601 and also only 1 record Latest of I/2023/400018/00097602 on this basis of Partner code.

I tried the following:

SQL

select InspNo,InputStr,PartnerCode,IsPush,CreaOn,IsPush,_
PushToPartner from PartnerIntegrationLog where IsPush is null _
and  (PartnerCode='ADR' OR PartnerCode='MFC') 
group by InspNo,InputStr,PartnerCode,IsPush,CreaOn,IsPush,_
PushToPartner
 order by CreaOn desc

But it gives duplicate record of both inspections.

What I have tried:

I tried many things but am not getting any success.

Posted 1-Nov-23 5:05am

Akshay malvankar

Updated 1-Nov-23 9:00am

Deeksha Shenoy

v3

Add a Solution

1 solution

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)

Richard Deeming · Accepted Answer · 2023-11-01T05:55:00

That's not how GROUP BY works; you're grouping by every column, so unless there are two identical records in the table, it will return every record.

Probably the simplest option would be to use ROW_NUMBER to partition the records:

SQL

WITH cte As
(
    SELECT
        InspNo,
        InputStr,
        PartnerCode,
        CreaOn,
        IsPush,
        PushToPartner,
        ROW_NUMBER() OVER (PARTITION BY PartnerCode ORDER BY CreaOn DESC) As RN
    FROM
        PartnerIntegrationLog 
    WHERE
        IsPush Is Null
    And 
        (PartnerCode = 'ADR' OR PartnerCode = 'MFC')
)
SELECT
    InspNo,
    InputStr,
    PartnerCode,
    CreaOn,
    IsPush,
    PushToPartner
FROM
    cte
WHERE
    RN = 1
;