Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DISTINCT Expand / Collapse
Author
Message
Posted Friday, September 25, 2009 12:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 10:53 PM
Points: 27, Visits: 202

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
Posted Friday, September 25, 2009 1:00 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen 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
Posted Friday, September 25, 2009 1:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 10:53 PM
Points: 27, Visits: 202
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 »

Add to briefcase

Permissions Expand / Collapse