Forum Discussion

saikumar86k's avatar
saikumar86k
Copper Contributor
Dec 23, 2024

Need Help To Get Last Record Based On Column Value

Hi , 

Below is the sample data, and I need to get the latest record based on last_modified_date, and [active/inactive] columns.

  1. ID, Name are the key columns and based on last modified date i need to get the last record 
  2. If Any of these key columns have 'Active' status, irrespective of last modified date, pull that record else take the latest record of inactive record.

the Ideal output should be: 111, AAA, Active,2024-04-21 (Since it has status of active on earlier dates)

 

2 Replies

  • IrfanDBA's avatar
    IrfanDBA
    Copper Contributor
    Feb 20, 2025

    This query should return the Active row if we have one otherwise returns the latest row based on Last_Modified_Date.

    ;WITH StatusRanking AS (
        SELECT 
            c_ID, 
            [C_Name], 
            [Active/InActive] , 
            Last_Modified_Date,
            ROW_NUMBER() OVER (
                PARTITION BY C_ID 
                ORDER BY 
                    CASE 
                        WHEN [Active/InActive]  = 'Active' THEN 1 
                        ELSE 2 
                    END, 
                    Last_Modified_Date DESC
            ) AS RowNum
        FROM #T
    )
    SELECT 
        C_ID, 
        [C_Name], 
        [Active/InActive], 
        Last_Modified_Date, RowNum
    FROM StatusRanking
    WHERE RowNum = 1;

  • rodgerkong's avatar
    rodgerkong
    Iron Contributor
    Dec 24, 2024
    1. Attach a row_number() that partitioned by key columns and ordered by last modified date to the data set as serials number.
    2. Filter all rows have 'Active' status.
    3. Get 'Inactive' rows which key columns match the 'Active' rows and sn equals to the 'Active' sn + 1.

    Code is here:

    CREATE TABLE #T
(
	C_ID int,
	C_Name varchar(10),
	[Active/Inactive] varchar(10),
	Last_Modified_Date datetime
);

INSERT INTO #T
VALUES
 (111, 'AAA', 'Active', '2024-04-22 19:00:51.863')
,(111, 'AAA', 'Inactive', '2024-04-21 19:00:51.863') --
,(111, 'AAA', 'Inactive', '2024-04-20 19:00:51.863')
,(111, 'AAA', 'Inactive', '2024-04-23 19:00:51.863')
;

WITH CTE
AS
(
	SELECT *
		,ROW_NUMBER() OVER(PARTITION BY C_ID, C_NAME ORDER BY Last_Modified_Date DESC) AS SN  --Without partition by will be OK
	FROM #T 
)
SELECT I.C_ID, I.C_Name, I.[Active/Inactive], I.Last_Modified_Date FROM CTE I
INNER JOIN
(
	SELECT * FROM CTE WHERE [Active/Inactive] = 'Active'
) A
ON I.C_ID = A.C_ID
AND I.C_Name = A.C_Name
AND I.SN = A.SN + 1
WHERE I.[Active/Inactive] = 'Inactive'

DROP TABLE #T

     

Resources