SQL Server creating view need a statement after having

mucho81 0
Viewed 44

I have this SQL Server table with this data:

ID   Name   Type
-------------------
1    ZZ     INPUT
2    AA     INPUT
3    CC     OUTPUT
4    ZZ     OUTPUT
5    AA     INPUT
6    CC     INPUT
7    KK     OUTPUT
8    TT     INPUT
9    CC     OUTPUT
10   DD     OUTPUT

As a result, I would like the only names that are used one time. And of the ones that are used ones only the OUTPUT type.

Correct result

ID   Name   Type
-------------------
1    KK     OUTPUT
2    DD     OUTPUT

I can do it by creating two views. Use the first view as a view in between. Can I achieve the result with one view?

sql-server sql
1 Answers

you only need a group by query and checks for count(*) = 1

select row_number() over (order by Name) as ID, Name
from   your_table
group by Name
having count(*) = 1
and    min(Type) = 'OUTPUT';
Squirrel0
Related