September 25, 2009 at 12:20 am
Hi Guru's
I can understand without sample data it's hard but I am sure for below issue, you guys doesn't need sample data.
I want to distinct either code or name. How can I do that.
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
FROM [dbo].[Depot] d
WHERE d.status = 'L'
Thanks,
D
September 25, 2009 at 1:00 am
Use of the distinct to avoid the same value repeated (duplicate) records in the full rows,
And try this
select distinct
isnull(d.[name],'Unknown')as depot_name
,isnull(d.,'Unknown') as depot_code
, d.[status]
,cast(floor(cast(d.[last_date] as float)) as smalldatetime)as last_date
FROM [dbo].[Depot] d
WHERE d.status = 'L'
September 25, 2009 at 1:25 am
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply