Rahul Hendawe Rahul Hendawe - 1 year ago 69
SQL Question

SELECT DISTINCT on one column or make duplicate entries _blank, while returning other columns

I have a query which returns about 10 columns.
Second column of the query output contains duplicate records, But if possible I need it to be distinct or make duplicate records blank of only second column.

my data looks something like that

HotelID ChainID
1041 10
1099 9
1297 10
1743 9
2000 10
241 10

The required ouput should be

HotelID ChainID
1041 10
1099 9

I have

Select Distinct id, val1
FROM table1 where active=0

I also tried using
Group By
but same result.

Thanks for the help!

Answer Source

Please run the below query to get the value it will full fill your need.

select hotelid,case when row_number()over(partition by ChainID order by HotelID)=1 then chainid else null end as ChainID  from table1 where active=0

out put

hotelid ChainID 
1099       9
1743      NULL
241        10
1041      NULL
1297      NULL
2000      NULL
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download