function that allows grouping of rows

Question

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'?

Take a look at this https://stackoverflow.com/questions/1466963/sql-row-number-function-in-where-clause — Sorix, Aug 14 '19 at 10:32

score 2 · Answer 1 · answered Aug 14 '19 at 10:33

2

Try this:

select * from (
Select
Count(*) over (partition by ID ) as [Row_Number],
Name,
ContractDescription,
Role
From table 
)t  where [Row_Number] = 1

answered Aug 14 '19 at 10:33

Red Devil

2,343
2
21
41

score 2 · Accepted Answer · answered Aug 14 '19 at 10:39

2

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

answered Aug 14 '19 at 10:39

Ilyes

14,640
4
29
55

Thanks to everyone for answering, they all worked but decided to go with this answer because it slotted into my query easier – Clem_Fandango Aug 14 '19 at 14:51

score 1 · Answer 3 · answered Aug 14 '19 at 10:30

1

You can check this option-

SELECT * 
FROM table
WHERE ID IN
(
    SELECT ID
    FROM table
    GROUP BY ID
    HAVING COUNT(*) = 1
)

answered Aug 14 '19 at 10:30

mkRabbani

16,295
2
15
24

score 1 · Answer 4 · answered Aug 14 '19 at 10:40

You can use a CTE to get all the ids of people who got only one contract and then just join the result of the CTE with your table.

;with cte as (
        select
            id
            ,COUNT(id) as no
        from @tbl
        group by id
        having COUNT(id) = 1
    )
    select
        t.id
        ,t.name
        ,t.ContractDescription
        ,t.role
    from @tbl t
    inner join cte
        on t.id = cte.id

EzLo · Answer 5 · 2019-08-14T11:11:06.190

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'?

You can use a windowed COUNT(). The key is the OVER() clause.

;WITH WindowedCount AS
(
    SELECT
        T.*,
        WindowCount = COUNT(1) OVER (PARTITION BY T.ID)
    FROM
        YourTable AS T
)
DELETE W FROM
    WindowedCount AS W
WHERE
    W.WindowCount > 1

The COUNT() will count the amount of rows for each different ID, so if the same ID appears in 2 or more rows, those rows will be deleted.

score 1 · Answer 6 · answered Aug 14 '19 at 10:59

Basically you need those record who have exactly one contract.

Just extend your script, (My script is not tested)

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

    From table    
    )

    select * from CTE c where [Row_Number]=1
    and not exists(select 1 from CTE c1 where c.id=c1.id and c1.[Row_Number]>1 )

function that allows grouping of rows

6 Answers6