SQL – find total records that occur before another

I have the following schema and want to find the number of clients that have a typeId of 10 appearing first. The answer with the following data would be 2 (client id 1000 and 1003 have a 10 on the 1st January and an 11 on 2nd January.

CREATE TABLE Event (ClientId int, TypeId int, Date smalldatetime)
INSERT Event (ClientId , TypeId, Date) VALUES (1000, 10, '1 JAN 12')
INSERT Event (ClientId , TypeId, Date) VALUES (1000, 11, '2 JAN 12')
INSERT Event (ClientId , TypeId, Date) VALUES (1001, 11, '1 JAN 12')
INSERT Event (ClientId , TypeId, Date) VALUES (1001, 10, '2 JAN 12')
INSERT Event (ClientId , TypeId, Date) VALUES (1002, 11, '1 JAN 12')
INSERT Event (ClientId , TypeId, Date) VALUES (1003, 10, '1 JAN 12')
INSERT Event (ClientId , TypeId, Date) VALUES (1003, 11, '2 JAN 12')

I have tried a few queries with ROW_NUMBER() but have got a bit lost.

  • Why does SQL keep creating a DF constraint?
  • SELECT from multiple tables without iteration
  • How to Model/Document Existing Stored Procedure Flow
  • Notification about Database Change
  • Why should I upgrade from SQL express to standard SQL server?
  • Creating and using a temporary table to return a specific row, instead of running the query repeatedly
  • 3 Solutions collect form web for “SQL – find total records that occur before another”

    try this:

    SELECT a.* FROM 
        (SELECT CLIENTID,TYPEID,MIN(DATE) AS DATE
         FROM EVENT
         GROUP BY CLIENTID, TYPEID)A
    JOIN
        (SELECT CLIENTID,MIN(DATE) AS DATE
         FROM EVENT
         GROUP BY CLIENTID)B
     ON   B.CLIENTID=A.CLIENTID
     AND  B.DATE=A.DATE
     WHERE TYPEID=10
    

    SQL Fiddle Demo

    You could try the following.

    • self join the table on ClientID
    • only when the typeID of the self joined table is larger
    • and the Date of the self joined table is larger

    SQL Statement

    SELECT e1.*
    FROM   Event e1
           INNER JOIN Event e2 ON e2.ClientID = e1.ClientID
                                  AND e2.TypeID > e1.TypeID
                                  AND e2.Date > e1.Date
    WHERE  e1.TypeID = 10
    

    Example Fiddle

    You can do this with two row_number()s:

    select count(*)
    from (select e.*,
                 row_number() over (partition by clientId order by date) as seqnum,
                 row_number() over (partition by clientId, typeid order by date) as ct_seqnum
          from event e
         ) e
    where seqnum = 1 and ct_seqnum = 1 and typeid = 10
    

    This is doing an overall ordering and an ordering by type. Your condition is equivalent to saying that both these are 1 on a row where the typeid is 10.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.