I have data as shown below.

from the data above when sys_update_date is same for a subscriber_no column then query should fetch the latest record based on the column sys_creation_date. similarly, when a subscriber_no has two rows have different date in sys_update_date itslef, then it should fetch the row with latest sys_update_date . please help me achieve this in a sql query .

expected output as below.

CodePudding user response：

SELECT SUBSCRIBER_NO, CUSTOMER_ID, SYS_CREATION_DATE, SYS_UPDATE_DATE, OPERATOR_ID
FROM
(
 SELECT 
    SUBSCRIBER_NO, 
    CUSTOMER_ID, 
    SYS_CREATION_DATE, 
    SYS_UPDATE_DATE, 
    OPERATOR_ID, 
    row_number() OVER (PARTITION BY SUBSCRIBER_NO ORDER BY SYS_UPDATE_DATE DESC, SYS_CREATION_DATE DESC) RN
    FROM <table>
) subselect
where subselect.RN=1;

CodePudding user response：

Here is a solution for your expected output:

I know this can be optimized further, but for initial draft of your requirements, please review. I hope this helps

Create table #Table1
(
    SUBSCRIBER_NO int, 
    CUSTOMER_ID int, 
    SYS_CREATION_DATE DateTime, 
    SYS_UPDATE_DATE DatetIme, 
    OPERATOR_ID int
)

INSERT INTO #Table1
    VALUES (61686209, 199041640, '19-JUN-22', '19-JUN-22', 611316378)
INSERT INTO #Table1
    VALUES (61686209, 145781645, '24-AUG-20', '19-JUN-22', 611316378)
INSERT INTO #Table1
    VALUES (61686210, 199044567, '19-JUN-22', '19-JUN-22', 611316379)
INSERT INTO #Table1
    VALUES (61686210, 145781234, '24-AUG-20', '17-JUN-22', 611316379)

SELECT
    p1.*
FROM #Table1 p1
WHERE p1.SYS_CREATION_DATE =
CASE
    WHEN (SELECT
                COUNT(p2.SYS_UPDATE_DATE)
            FROM #Table1 p2
            WHERE p2.SYS_UPDATE_DATE = p1.SYS_UPDATE_DATE)
        > 1 THEN (SELECT
                MAX(p2.SYS_CREATION_DATE)
            FROM #Table1 p2
            WHERE p2.SYS_UPDATE_DATE = p1.SYS_UPDATE_DATE)
    ELSE p1.SYS_CREATION_DATE
END
AND p1.SYS_UPDATE_DATE =
CASE
    WHEN (SELECT
                COUNT(p2.SUBSCRIBER_NO)
            FROM #Table1 p2
            WHERE p2.SUBSCRIBER_NO = p1.SUBSCRIBER_NO)
        > 1 THEN (SELECT
                MAX(p2.SYS_UPDATE_DATE)
            FROM #Table1 p2
            WHERE p2.SUBSCRIBER_NO = p1.SUBSCRIBER_NO)
    ELSE p1.SYS_UPDATE_DATE
END
    
If(OBJECT_ID('tempdb..#Table1') Is Not Null)
Begin
DROP TABLE #Table1
END