Sorry this is the right answer, I think.

if you want distinct on one column then you will have multiple other columns for that column.

e.g. if you are asking for distinct name then against one name there would be multiple codes and dates. in such case you have to select one and ignore the others.

i am selecting the data on the basis of Last_date for distinct name.

declare @Depot table (name nvarchar(100), code nvarchar(100), last_date datetime, status nvarchar(5))

insert into @Depot

select 'abc', '789', getdate(), 'L' union all

select 'abc', null, getdate(), 'L' union all

select 'xyz', '123', getdate(), 'L' union all

select 'xyz', '123', getdate(), 'L' union all

select null, '123', getdate(), 'L' union all

select 'LMN', null, getdate(), 'L' union all

select 'abc', '456', getdate(), 'L'

select depot_name, depot_code, status, last_date from

(SELECT (coalesce(nullif(d.[name],''),'Unknown'))as depot_name

,coalesce(nullif(d.,''),'Unknown') as depot_code

, d.[status]

,cast(floor(cast(d.[last_date] as float)) as smalldatetime)as last_date

, row_number() over (partition By name order by last_date desc) r

FROM @Depot d

WHERE d.status = 'L') x

where r =1