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