Sql to get latest record under some condition

[tong123123]

I want to get record with following condition
each contract no., if has estate = 'LR', then get the record with latest build date before 'LR', and if have two record with same date, then choose type = 'X' first.
If no estate = 'LR', and choose the record with latest build date, if have same build date, choose type = 'X' first.

sample input and sampe output is attached.

The table can be created by the following sql:
CREATE TABLE [dbo].[testing](
[ID] [int] IDENTITY(1,1) NOT NULL,
[contract no.] varchar(10) not null,
[estate] varchar NOT NULL,
[type] varchar NOT NULL,
[build date] [datetime] not null,
remarks varchar(20) null
PRIMARY KEY CLUSTERED
(
[ID] ASC
)
) ON [PRIMARY]
GO

and data are inserted as follow:
insert into testing("contract no.", estate, type,"build date", remarks) values ('00001','1a','B','20241101','ewr');
insert into testing("contract no.", estate, type,"build date", remarks) values ('00001','LR','C','20240101','erew');
insert into testing("contract no.", estate, type,"build date", remarks) values ('00001','1b','X','20231005','wer');
insert into testing("contract no.", estate, type,"build date", remarks) values ('00001','1c','C2','20231005','');
insert into testing("contract no.", estate, type,"build date", remarks) values ('00002','2a','B','20241005','');
insert into testing("contract no.", estate, type,"build date", remarks) values ('00002','2b','C','20240205','');
insert into testing("contract no.", estate, type,"build date", remarks) values ('00003','3a','A1','20240602','');
insert into testing("contract no.", estate, type,"build date", remarks) values ('00003','3b','X','20240602','');
insert into testing("contract no.", estate, type,"build date", remarks) values ('00004','4a','D','20240502','');
insert into testing("contract no.", estate, type,"build date", remarks) values ('00004','4b','X','20230101','');

[Ifor]

Forum Etiquette: How to post data/code on a forum to get the best help – SQLServerCentral

[Ifor]

Varchars should always have a length and reserved words, spaces etc in column names are not a good idea.

WITH LRDates
AS
(
	SELECT ID, [contract no.], estate, [type], [build date], remarks
		,MAX(IIF(estate = 'LR', [build date], '1900')) OVER (PARTITION BY [contract no.]) AS LRDate
	FROM dbo.testing
)
,ContractOrder
AS
(
	SELECT ID, [contract no.], estate, [type], [build date], remarks
		,ROW_NUMBER() OVER (PARTITION BY [contract no.] ORDER BY [build date] DESC, IIF([type] = 'X', 0, 1)) AS rn
	FROM LRDates
	WHERE [build date] < IIF(LRDate = '1900', '9999', LRDate)
)
SELECT ID, [contract no.], estate, [type], [build date], remarks
FROM ContractOrder
WHERE rn = 1;

[harishgg1]

Hi

Hope this helps

If any of you wants to understand what
IFOR is doing

You can add that technique to your
T SQL
Arsenal

[harishgg1]

hi

hope this helps

Another Way

changed [contract no.] to cn
changed [build date] to bd

;WITH lr_build AS (  
    SELECT   
        cn,  
        bd AS lr_build_date  
    FROM   
        testing  
    WHERE   
        estate = 'LR'  
),  
ranked_records AS (  
    SELECT   
        t.cn,  
        t.bd,  
        t.type, 
		t.estate,
		t.remarks,
        ROW_NUMBER() OVER (  
            PARTITION BY t.cn   
            ORDER BY   
                CASE   
                    WHEN l.lr_build_date IS NOT NULL AND t.bd < l.lr_build_date THEN 0   
                    ELSE 1   
                END,  
                t.bd DESC,  
                CASE t.type   
                    WHEN 'X' THEN 0   
                    ELSE 1   
                END  
        ) AS row_num  
    FROM   
        testing t  
        LEFT JOIN lr_build l   
        ON t.cn = l.cn  
)  
SELECT   
    cn,  estate ,    type  ,
    bd,  remarks

FROM   
    ranked_records  
WHERE   
    row_num = 1;