How to add an additional column in a table by comparing the values in a different column

Question

I have a table with the following records

id name city

1 aaa NY

2 bbb NY

3 ccc LA

4 ddd LA

5 eee NY

I want the table with an additional column by comparing the 'city' column. The values in the col4 should have '1' for every unique value in 'city' column and '0' for the repeating values in 'city' column.

id name city col4

1 aaa NY 1

2 bbb NY 0

3 ccc LA 1

4 ddd LA 0

5 eee NY 0

I hope to get some help.

Thanks

Answer 1

Select id, name, city
    , Case 
        When Exists(    Select 1
                        From Table As T1
                        Where T1.Id < T.Id
                            And T1.city = T.city ) Then 0
        Else 1
        End As Col4
From Table As T

Answer 2

this will give you MUCH better performance than a sub query, which is once run for each row in the outer query:

DECLARE @YourTable table (id int, name varchar(5), city varchar(5))
INSERT @YourTable VALUES (1,'aaa','NY')
INSERT @YourTable VALUES (2,'bbb','NY')
INSERT @YourTable VALUES (3,'ccc','LA')
INSERT @YourTable VALUES (4,'ddd','LA')
INSERT @YourTable VALUES (5,'eee','NY')

    SELECT
        y.id, y.name, y.city
            ,CASE
                 WHEN y.ID=dt.MinID THEN 1
                 ELSE 0
             END AS col4
        FROM @YourTable    y
            INNER JOIN (SELECT
                            City, MIN(id) AS MinID
                            FROM @YourTable
                            GROUP BY City
                       ) dt ON y.City=dt.City
        ORDER BY id

OUTPUT:

id          name  city  col4
----------- ----- ----- -----------
1           aaa   NY    1
2           bbb   NY    0
3           ccc   LA    1
4           ddd   LA    0
5           eee   NY    0

(5 row(s) affected)