CODEWITHSUNDEEP
sql-servert-sql
ricardomadaleno
ricardomadaleno

Reputation: 23

T-SQL identify first time value appears in column

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?

Upvotes: 0

Views: 2548

Answers (4)

Tony S.
Tony S.

Reputation: 21

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
;

Upvotes: 1

Paweł Dyl
Paweł Dyl

Reputation: 9143

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

Upvotes: 2

shawnt00
shawnt00

Reputation: 17935

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

Upvotes: 0

Lamak
Lamak

Reputation: 70668

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;

Upvotes: 2

Related Questions