Flag “yes/No” if the subsequent row has same ID

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?

Thanks

Answer

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;