T-SQL identify first time value appears in column

Question

I am working in Excel 2013 and connecting to SQL Server with an ODBC connection

I have a table like this:

id    PhoneNumber    Caller
--------------------------------
1     915869850      John
2     912586985      Mary
3     963285874      John
4     915869850      Richard
5     965878965      James
6     925869753      Richard
8     963285874      James

and I need to be add a column that identifies the first time a phone number is called and ignores it on the subsequent times...

Like this:

id    PhoneNumber    Caller    First Time
-----------------------------------------
1     915869850      John      1
2     912586985      Mary      1
3     963285874      John      1
4     915869850      Richard   0
5     965878965      James     1
6     925869753      Richard   1
8     963285874      James     0

Is it possible to do so?

Can you help me?

Possible duplicate of [Get top 1 row of each group](http://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Tab Alleman, Aug 02 '16 at 18:42
@TabAlleman not really a duplicate situation because on that post the op only wants the output to show the top 1 row of each group, while as in my post i need to add a column to identify the first instance of the group, but still output the whole table as normal... — ricardomadaleno, Aug 03 '16 at 08:30
For purposes of Stack Overflow, it is a duplicate, since the technique used to solve that problem also solves your problem. The root problem is the same: How to identify the top 1 of a group. Whether you then use it to only display the top 1, or to add a column is a trivial difference. — Tab Alleman, Aug 03 '16 at 12:55
@TabAlleman for people that understand what the code does, no it doesn't make a difference, but for someone that doesn't know much of SQL and is looking for a solution based on the output being similar to what they need, then there is a world of difference... — ricardomadaleno, Aug 03 '16 at 17:01

score 2 · Answer 1 · answered Aug 02 '16 at 16:42

2

Assuming SQL Server 2005+ you can use ROW_NUMBER and then a CASE expression:

WITH CTE AS
(
    SELECT  *,
            RN = ROW_NUMBER() OVER(PARTITION BY PhoneNumber ORDER BY id)
    FROM dbo.Phones
)
SELECT  id,
        PhoneNumber,
        [Caller],
        CASE WHEN RN = 1 THEN 1 ELSE 0 END [First Time]
FROM CTE;

answered Aug 02 '16 at 16:42

Lamak

69,480
12
108
116

You could also convert RN to a bit instead of using a case expression. Convert(bit, RN) as FirstTime. – Sean Lange Aug 02 '16 at 18:09
@SeanLange Wouldn't that return `1` for every value? – Lamak Aug 02 '16 at 18:11
LOL yes I suppose it would unless you used some math RN -1 (which of course would be totally pointless and silly). – Sean Lange Aug 02 '16 at 18:13

score 2 · Accepted Answer · answered Aug 02 '16 at 16:59

2

Use ROW_NUMBER() window function like below:

SELECT *, CASE WHEN (ROW_NUMBER() OVER
    (PARTITION BY PhoneNumber ORDER BY id))=1 THEN 1 ELSE 0 END FirstTime
FROM Src
ORDER BY id

answered Aug 02 '16 at 16:59

Paweł Dyl

8,888
1
11
27

This works great!! would it be possivel to add a condition so that it would identify the first time it happens on a week day? – ricardomadaleno Nov 03 '17 at 11:37

score 1 · Answer 3 · answered Aug 02 '16 at 17:17

1

Another solution:

WITH FirstCallList AS
(
SELECT 
    MIN([id]) AS FirstIdForNumber
FROM
    Calls
GROUP BY
    PhoneNumber
)
SELECT
    Calls.id
    ,Calls.PhoneNumber
    ,Calls.Caller
    ,CASE WHEN FirstCallList.FirstIdForNumber IS NULL THEN 0 ELSE 1 END AS FirstTime
FROM 
    Calls
    LEFT OUTER JOIN FirstCallList ON Calls.id = FirstCallList.FirstIdForNumber
ORDER BY
    Calls.id
;

answered Aug 02 '16 at 17:17

Tony S.

21
3

No need for an outer join here. – shawnt00 Aug 03 '16 at 03:41
Not following. OP's requirements are to see rows for calls that were NOT first, as well as those that were. Post example of how you would change join and still get same result? – Tony S. Aug 04 '16 at 11:54
I misread and yours is correct. My excuse is going to be that viewing code on my phone's screen is a small hassle and I must have missed you weren't joining oh phone number. Here's what I was picturing: http://rextester.com/LKDV75532 – shawnt00 Aug 04 '16 at 15:28

score 0 · Answer 4 · answered Aug 02 '16 at 16:44

The classic approach to this was a self join or a scalar subquery like this:

select id, PhoneNumber,
    case
        when id = (
            select min(p2.id) from <Phones> p2
            where p2.PhoneNumber = p.PhoneNumber
        ) then 1 else 0
    end as [First Time]
from <Phones> p

T-SQL identify first time value appears in column

4 Answers4