Remove duplicate recoed with exclude one column

Question

Hello friends,

I have a query like:

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

And it's output:

But I want output like:

or

What should I do?
How can I get above output using SQL query or from c# code?

Thanks.

Jignesh Kumar · Answer

Please try this one select distinct id, Name , DENSE_RANK()over( order by id) as DisplayName from MyTable

Suraj Kumar · Answer

Hello, You can write query as below select * into #temp from( -- Taken Output In a temp table. 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 )tblA order by DisplayOrder desc -- Change Here If you want Display Order 2 remove desc select distinct Id, Name, 0 AS DisplayOrder into #distinct from #temp -- Taken distinct record from above table. UPDATE #distinct SET #distinct.DisplayOrder = #temp.DisplayOrder FROM #temp INNER JOIN #distinct ON #distinct.Id = #temp.Id select * from #distinct --For Output DROP TABLE #temp DROP TABLE #distinct Thanks Suraj

Jignesh Kumar · Answer

Glad to know it helps you. Thank you for marking answer.

Bhavesh Jadav · Answer

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.

Yogesh Ghorpade · Answer

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

Pravat Kumar · Answer

Hello Bhavesh, You can try this one select * INTO #test 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 )a select * from #test SELECT DISTINCT id ,NAME ,( SELECT TOP 1 MIN(DisplayOrder) FROM #test WHERE Id = i.Id ) AS DisplayName FROM #test i SELECT DISTINCT id ,NAME ,( SELECT TOP 1 MAX(DisplayOrder) FROM #test WHERE Id = i.Id ) AS DisplayName FROM #test i order by DisplayName asc

Suraj Kumar · Answer

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

Remove duplicate recoed with exclude one column

Answers (7)