CODEWITHSUNDEEP
sql
Stephen Gardner
Stephen Gardner

Reputation: 13

Identify if a value is appearing for the first time in a column

I'm trying to identify if the value of a column (let's call it status for now) is appearing for the first time for a given ID, or if it's previously held that value before. If its the first time that the status is equal to 1 or 2, I'd like to return a 1=Y or a 0=N. The results below are what I'm looking to recreate. Any help is greatly appreciated. Thanks

ID  Date      Status First Time

1  1/1/2017     1   Y    
2   1/1/2017    0   N    
3   1/1/2017    0   N    
4   1/1/2017    2   Y    
5   1/1/2017    0   N

1   2/1/2017    0   N   
2   2/1/2017    0   N    
3   2/1/2017    1   Y    
4   2/1/2017    0   N    
5   2/1/2017    1   Y    

1   3/1/2017    2   N    
2   3/1/2017    0   N    
3   3/1/2017    0   N    
4   3/1/2017    1   N    
5   3/1/2017    1   N

Upvotes: 0

Views: 3260

Answers (4)

Uueerdo
Uueerdo

Reputation: 15961

SELECT t1.ID, t1.Date, t1.status
, CASE WHEN firsts.ID IS NULL THEN 'N' ELSE 'Y' END AS `First Time`
FROM theTable AS t1
LEFT JOIN
   (SELECT ID, MIN(Date) AS fDate
   FROM theTable AS t0
   WHERE status IN (1, 2)
   GROUP BY ID
) AS firsts
ON t1.ID = firsts.ID AND t1.status IN (1, 2) AND t1.Date = firsts.fDate
;

You can use a subquery to identify the firsts, and join to the original records to "add" that information.

Note: This could lead to some redundant firsts if an ID's first 1 and 2 status values occur on the same day; but the current definition of "first" does't really state which status would be "first".

Upvotes: 1

ccarpenter32
ccarpenter32

Reputation: 1077

Working solution, if I understand the problem correctly:

Test Table Creation:

WITH Test (ID, [Date], [Status]) AS (
    SELECT * FROM (
        VALUES
            (1,Convert(datetime, '1/1/2017', 120),1), 
            (2,Convert(datetime, '1/1/2017', 120),0), 
            (3,Convert(datetime, '1/1/2017', 120),0), 
            (4,Convert(datetime, '1/1/2017', 120),2), 
            (5,Convert(datetime, '1/1/2017', 120),0), 
            (1,Convert(datetime, '2/1/2017', 120),0), 
            (2,Convert(datetime, '2/1/2017', 120),0), 
            (3,Convert(datetime, '2/1/2017', 120),1), 
            (4,Convert(datetime, '2/1/2017', 120),0), 
            (5,Convert(datetime, '2/1/2017', 120),1), 
            (1,Convert(datetime, '3/1/2017', 120),2), 
            (2,Convert(datetime, '3/1/2017', 120),0), 
            (3,Convert(datetime, '3/1/2017', 120),0), 
            (4,Convert(datetime, '3/1/2017', 120),1), 
            (5,Convert(datetime, '3/1/2017', 120),1)
    ) AS A (Column1, Column2, Column3)
)

Query:

SELECT id,
    [Date],
    (SELECT [Status] FROM Test b 
        WHERE a.ID = b.ID 
        AND a.[Date] = b.[Date]) AS [Status], 
    CASE WHEN (SELECT MIN([Date]) 
        FROM Test 
        WHERE [Status] IN (1, 2)
        AND ID = a.ID
        GROUP BY ID
    ) = a.[Date] THEN 'Y'
    ELSE 'N'
    END AS FirstTime
FROM Test a 
GROUP BY ID, [Date]

Upvotes: 0

Ryan B.
Ryan B.

Reputation: 3665

Something like this may work for you. The ROW_NUMBER function ordered by date and partitioned by status will add a counter that, within each group of similar statuses, will increment from 1 according to ascending date.

Combining this with a CASE statement, we can say that the status a 'first time' when that counter is either a 1 or two. 

SELECT 
    *
    , CASE WHEN ROW_NUMBER() OVER (PARTITION BY [Status] ORDER BY [date]) IN (1,2) THEN 'Y' ELSE 'N' END
FROM [MY_TABLE]

EDIT: As noted below, this is a SQL Sever solution. You would make some tweaks to get it in MYSQL.

Upvotes: 0

CrimsonKing
CrimsonKing

Reputation: 2896

Just check if Status is 1 or 2, and whether there exists a record with the same ID and at an earlier date with Status 1 or 2.

select ID, Date, Status,
    case 
        when a.Status in (1,2) and not exists(select * from table b where a.ID = b.ID and b.Status in (1,2) and b.Date < a.Date) then 'Y' 
        else 'N' 
    end as First
from table a

Upvotes: 0

Related Questions