I'm looking to use a case expression that will return the number 1 once for each unique ID that I have listed and a 0 if the ID is the same as the one above it. The column is a text column that currently either has 'N' or 'Y'.

Below is an example of what my data looks like. I'd like to keep it as short as possible because this will be used within other SQL.

The SQL: select case when Value = 'Y' then 1 else 0 end

Returns:

ID      Value
48719   Y      -- returns 1
48719   Y      -- returns 1
48719   Y      -- returns 1 
55555   Y      -- returns 1

What'd I'd like to see.

ID      Value
48719   Y      -- return 1
48719   Y      -- return 0
48719   Y      -- return 0
55555   Y      -- return 1

This is also a possible scenario. For the first ID, I'd like to have any of the three rows return a 1, but the other two to show 0. Since the case statement would return a 0 for the first row, I can I have it select only one 1 for all rows with that ID.

ID      Value
48719   N      -- returns 1
48719   Y      -- returns 0
48719   Y      -- returns 0 
55555   Y      -- returns 1