Remove duplicate recoed with exclude one column

Hello friend,

 

I am satisfied with all of your answers. All are giving a right solution as per my requirement.

 

thank you so much to all of you.

select distinct Temp.Id,Temp.Name, DENSE_RANK()over(order by Temp.Id) from

(select '1101' as Id, 'prd1' as Name, '1' as DisplayOrder

union ALL

select '1102' as Id, 'prd2' as Name,'2' as DisplayOrder

union ALL

select '1103' as Id, 'prd3' as Name, '3' as DisplayOrder

union ALL

select '1102' as Id, 'prd2' as Name, '4' as DisplayOrder

) as Temp

Hello Bhavesh,

 

You can try this one

 

Hello,

 

If your data is already in the table then you can write query in short as below it will give correct DisplayOrder which will be already exist in the table. It will run even Display order in table not will be in sequence. For example. display order may not always be in sequence (1, 2, 3, 4, 9, 7, 9) etc.

 

SELECT DISTINCT Id, Name, 0 AS DisplayOrder INTO #distinct FROM MyTable -- Taken distinct record from above table.

UPDATE #distinct

SET #distinct.DisplayOrder = MyTable.DisplayOrder

FROM MyTable

INNER JOIN #distinct

ON #distinct.Id = MyTable.Id

SELECT * FROM #distinct

DROP TABLE #distinct

 

Thanks

Suraj

Hello,

You can write query as below

 

 

 

Thanks

Suraj