Flag "yes/No" if the subsequent row has same ID-CodePudding

I have data like this. If the same id is present in the next row, I want to flag as Yes. If it is not present then Make it as 'No'. Can you kindly help me with the query?

enter image description here

Thanks

CodePudding user response：

The problem with multiple rows for the same ID and no other column that can be used to futher narrow the sort sequence is that you need an order you can rely on. As the typical sulution for the general task to compare with the next row's ID is LEAD, you'll have two ORDER BY clauses in your query, one for LEAD and one for the query result, and you want to force them somehow to obey the same sort order. ORDER BY id is not sufficent.

The best and easiest approach is probably to number the rows first, and then work on this data set.

with numbered
(
  select
    id,
    row_number() over (order by id) as rn
  from mytable
)
select
  id,
  case when id = lead(id) over (order by rn) then 'yes' else 'no' end as flag
from numbered
order by rn;

CodePudding user response：

You can use LEAD, which gets the value of the next row.

SELECT 
    CASE
       WHEN ID = LEAD(ID) OVER (ORDER BY ID) THEN 'yes'
       ELSE 'no'
    END
FROM [MyTableName]
ORDER BY ID

You can read more about LEAD here.

CodePudding user response：

select ID, Lag(ID) OVER(Order by ID desc) as [NextVal], 
case when 
    Lag(ID) OVER(Order by ID desc) = ID THEN 'yes'
    ELSE 'no'
END as 'FLAG'
from tableName
order by 1 , 2