SQL 1 row twice

Question

I have SQL table what looks like:

+----------+-----------+
| ID       | Direction | 
+----------+-----------+
| 1        | left      | 
| 1        | null      | 
| 2        | left      | 
| 2        | null      | 
| 3        | null      | 
| 4        | left      | 
| 4        | null      |
| 5        | null      |
+----------+-----------+

I want to show each value only once:

• If there will be ID 1 with Direction null and left, then show only ID 1 with direction left.
• If there will be ID 1 only with null value, show it with null value.

Answer 1

Why wont below query work:

select id,max(dir)
from #temp
group by id

below is test data:

create table #temp
(
id int,
dir char(10)
)

insert into #temp
select 1,'left'
union all
select 1,null
union all
select 2,null
union all
select 3,'right'
union all
select 3,null
union all
select 3,null


select id,max(dir)
from #temp
group by id

aggregate functions will ignore null,below is the output:

Answer 2

select distinct *, row_number() over (partition by id order by ,Direction )as row1 into #any_table from #your_table_name

select * from #any_table where row1 =1

Answer 3

Use a common table expression (cte):

with cte as 
(
    Your huge select...
)
select *
from cte t1
where t1.Direction = 'left'
  or not exists (select * from cte t2
                 where t2.kanbanid = t1.kanbanid 
                   and t2.Direction = 'left')

I.e. if your select has Direction 'left' for a kanbanid, return that row. Also return that row if same kanbanid has no Direction 'left' at all.