Handle Unique and Duplicate records in Update statement in Postgresql

Question

I have a table test in postgresql 9.3 having fields ID,sch_item_number and dup_test.Schema name is tablet. I need to update dup_test on certain conditions i.e. If there are only unique sch_item_number then the dup_test will be 'diff'.Incase of duplicate sch_item_number, the first record of that duplicate sch_item_number must be 'diff' and the rest should be 'same'. Please find the attached screenshot. I have shared the fiddle too. http://sqlfiddle.com/#!9/1b135c

Answer 1

Sch_item_number=Item

You can try below sql:

select a.item, (Case a.sno when 1 then 'diff' else 'same' end) as dup_test from ( select ROW_NUMBER() OVER (PARTITION BY Item ORDER BY Item) as Sno, * from table )a group by a.item, (Case a.sno when 1 then 'diff' else 'same' end)