DISTINCT

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » DISTINCT

DISTINCT

Rate Topic

Display Mode

Topic Options

Author

Message

dreamslogic

Posted Friday, September 25, 2009 12:20 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, March 15, 2012 3:31 AM
Points: 27, Visits: 197

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.[code],''),'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

Post #793669

arun.sas

Posted Friday, September 25, 2009 1:00 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, April 19, 2012 10:25 PM
Points: 1,231, Visits: 3,483

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.[code],'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'

Post #793681

dreamslogic

Posted Friday, September 25, 2009 1:25 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, March 15, 2012 3:31 AM
Points: 27, Visits: 197

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.[code],''),'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

Post #793691

« Prev Topic | Next Topic »

Permissions