function that allows grouping of rows

I’m using SQL Server Management Studio 2012. I have a similar looking output from a query shown below. I want to eliminate someone from the query who has 2 contracts.

Select
Row_Number() over (partition by ID ORDER BY  ContractypeDescription DESC) as [Row_Number],
Name,
ContractDescription,
Role

From table

Output

Row_Number   ID     Name     Contract Description   Role
    1       1234    Mike          FullTime          Admin
    2       1234    Mike          Temp              Manager
    1       5678    Dave          FullTime          Admin
    1       9785    Liz           FullTime          Admin

What I would like to see

 Row_Number   ID    Name     Contract Description   Role
    1       5678    Dave          FullTime          Admin
    1       9785    Liz           FullTime          Admin

Is there a function rather than Row_Number that allows you to group rows together so I can then use something like ‘where Row_Number not like 1 and 2’?

Answer

You can use HAVING as

SELECT ID,
       MAX(Name) Name,
       MAX(ContractDescription) ContractDescription,
       MAX(Role) Role
FROM t
GROUP BY ID
HAVING COUNT(*) = 1;

Demo

Advertisement

Answer