SQL - Find unique min value and associated columns

I have a set of data as below, showing the history of who has done what with a record. The unique identifier for each record is shown in 'ID' and 'Rec No' is the sequential number assigned to each interaction with the record.

ID  Rec No  Who Type
1   1   Bob New
1   2   Bob Open
1   3   Bob Assign
1   4   Sarah   Add
1   5   Bob Add
1   6   Bob Close
2   1   John    New
2   2   John    Open
2   3   John    Assign
2   4   Bob Assign
2   5   Sarah   Add
2   6   Sarah   Close
3   1   Sarah   New
3   2   Sarah   Open
3   3   Sarah   Assign
3   4   Sarah   Close

I need to find all o开发者_如何学Gof the 'Assign' operations. However where multiple 'Assign' are in a certain ID, I want to find the first one. I then also want to find the name of the person who did that.

So ultimately from the above date I would like the output to be-

Who Count (assign)
Bob 1
John    1
Sarah   1

The code I have at the moment is-

SELECT IH.WHO, Count(IH.ID)
FROM Table.INCIDENTS_H IH
WHERE (IH.TYPE = Assign)
GROUP BY IH.WHO

But this gives the output as-

Who Count (assign)
Bob 2
John    1
Sarah   1

As it is finding that Bob did an assign on ID 2, Rec No 4.

Any help would be appreciated. I am using MS SQL.

I think something like this is what you are after:

select 
  who, count(id) 
from (
  select ID, Who, row_number() over (partition by ID order by Rec) [rownum]
  from Table.INCIDENTS_H IH
  WHERE (IH.TYPE = Assign)
) a
where rownum = 1
group by who

This should count only the first Assign (ordered by Rec) within each ID group.

This ought to do it:

SELECT IH.WHO, COUNT(IH.ID)
FROM INCIDENTS_H IH
JOIN (
  SELECT ID, MIN([Rec No]) [Rec No] 
  FROM INCIDENTS_H
  WHERE ([Type] = 'Assign')
  GROUP BY ID
 ) IH2
 ON IH2.ID = IH.ID AND IH2.[Rec No] = IH.[Rec No]
GROUP BY IH.WHO

You can use row_number to accomplish this

WITH INCIDENTS_H  as (
SELECT
1 as ID, 1 as RecNo, 'Bob' as Who, 'New' as type
UNION ALL SELECT 1,   2,   'Bob','Open'
UNION ALL SELECT 1,   3,   'Bob','Assign'
UNION ALL SELECT 1,   4,   'Sarah','Add'
UNION ALL SELECT 1,   5,   'Bob','Add'
UNION ALL SELECT 1,   6,   'Bob','Close'
UNION ALL SELECT 2,   1,   'John','New'
UNION ALL SELECT 2,   2,   'John','Open'
UNION ALL SELECT 2,   3,   'John','Assign'
UNION ALL SELECT 2,   4,   'Bob','Assign'
UNION ALL SELECT 2,   5,   'Sarah','Add'
UNION ALL SELECT 2,   6,   'Sarah','Close'
UNION ALL SELECT 3,   1,   'Sarah','New'
UNION ALL SELECT 3,   2,   'Sarah','Open'
UNION ALL SELECT 3,   3,   'Sarah','Assign'
UNION ALL SELECT 3,   4,   'Sarah','Close')
, GetTheMin AS (
SELECT 
    ROW_NUMBER() over (partition by id order by recno) row,
    ID,
    RecNo,
    Who,
    type

FROM 
    INCIDENTS_H
WHERE
    type = 'Assign'
)
SELECT Who,
COUNT(ID)
FROM GetTheMin
WHERE
    row = 1
GROUP BY 
    who

OR you can use CROSS Apply

SELECT 
    who,
    COUNT(id) id
FROM
(SELECT DISTINCT
    MinValues.*
FROM
    INCIDENTS_H h
    CROSS APPLY ( SELECT TOP 1 *
                 FROM INCIDENTS_H h2
                 WHERE h.id = h2.id
                ORDER BY ID, RecNo asc) MinValues) getTheMin
GROUP BY WHO

Or you can use Min which uses standard SQL John Fisher's answer demonstrates

Here's a view of everything in the table which should match your "first assign" requirement:

select a.*
from Table.INCIDENTS_H a
inner join
(select ID, min([Rec No]) [Rec No] from Table.INCIDENTS_H where Type = 'Assign' group by ID) b
on a.ID = b.ID and a.[Rec No] = b.[Rec No]

Result:

ID  Rec No  Who Type
1   3   Bob Assign
2   3   John    Assign
3   3   Sarah   Assign

select * from
  (select
     id, rec_no, who
   from
     operation_history
   where
     type = 'Assign'
   order by rec_no asc) table_alias
group by
  id
order by id asc

Tested and here are the results:

id  rec_no  who
1      3    Bob
2      3    John
3      3    Sarah

(Code not specific to SQL Server)

Here is the query with virtual test data that were mentioned in the original post:

with T (ID,  RecNo,  Who, Type) as
(   
select 1,   1,   'Bob',     'New'   union all
select 1,   2,   'Bob',     'open'  union all
select 1,   3,   'Bob',     'Assign' union all
select 1,   4,   'Sarah',   'Add'   union all
select 1,   5,   'Bob',     'Add'   union all
select 1,   6,   'Bob',     'Close' union all
select 2,   1,   'John',    'New'   union all
select 2,   2,   'John',    'Open'  union all
select 2,   3,   'John',    'Assign' union all
select 2,   4,   'Bob',     'Assign' union all
select 2,   5,   'Sarah',   'Add'   union all
select 2,   6,   'Sarah',   'Close' union all
select 3,   1,   'Sarah',   'New'   union all
select 3,   2,   'Sarah',   'Open'  union all
select 3,   3,   'Sarah',   'Assign' union all
select 3,   4,   'Sarah',   'Close'
)

select top 1 with ties * 
from T
where Type = 'Assign'
order by row_number() over(partition by ID order by RecNo)

The "select" statement that can be applied to the real situation from the question might look like:

SELECT TOP 1 WITH TIES 
    IH.ID, IH.[Rec No], IH.WHO, IH.TYPE
FROM Table.INCIDENTS_H IH
WHERE IH.TYPE = 'Assign'
ORDER BY ROW_NUMBER() OVER(PARTITION BY IH.ID ORDER BY IH.[Rec No]);