InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 17:41:49 GMT20http://www.sqlservercentral.com/Forums/Topic793669-338-1.aspxSorry 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 allselect 'abc', null, getdate(), 'L' union allselect 'xyz', '123', getdate(), 'L' union allselect 'xyz', '123', getdate(), 'L' union allselect null, '123', getdate(), 'L' union allselect 'LMN', null, getdate(), 'L' union allselect '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') xwhere r =1Fri, 25 Sep 2009 01:25:37 GMTdreamslogichttp://www.sqlservercentral.com/Forums/Topic793669-338-1.aspxUse of the distinct to avoid the same value repeated (duplicate) records in the full rows,And try this[code]select distinctisnull(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_dateFROM [dbo].[Depot] dWHERE d.status = 'L'[/code]Fri, 25 Sep 2009 01:00:31 GMTarun.sashttp://www.sqlservercentral.com/Forums/Topic793669-338-1.aspxHi Guru'sI 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_dateFROM [dbo].[Depot] dWHERE d.status = 'L'Thanks,DFri, 25 Sep 2009 00:20:22 GMTdreamslogic