How to set the Flag for Only one Record

Question

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246986 More actions · Answer 1

As simple as this, or more sophistication required?

IF OBJECT_ID('tempdb..#test', 'U') IS NOT NULL

DROP TABLE #test;

CREATE TABLE #test (Id INT, Value VARCHAR(20));

INSERT #test

(Id, Value)

VALUES (1, 'Season 16'),

(2, 'Season 16'),

(2, 'Season 15'),

(3, 'Season 15'),

(4, 'Season 16');

SELECT t.Id

, Flag = IIF(COUNT(1) > 1, 0, 1)

FROM #test t

GROUP BY t.Id;

By the way, in future, please include set-up code, as above, when posting questions like this.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Con Alexis Old Hand Points: 331 More actions · Answer 2

I notice that you're looking for multiple seasons for same ID

I.e. If ID has both Season16 and Season 15 then Flag is 0 else 1.

Since row with id = 3 has both records as "Season 15", you want the flag to be "1"

Slight modification to Phil's answer. Including "distinct" on the count would be required.

SELECT t.Id

, Flag = IIF(COUNT( distinct value) > 1, 0, 1)

FROM #test t

GROUP BY t.Id;