'I need first 2 occurrences of duplicate id in the output(image attached) using sql

enter image description here

want first 2 occurrence of duplicate ID'S in the output, desired output in image attached. Please help

sqlduplicates


Solution 1:[1]

I'm not sure what you meant by "1st occurrence" because sql select queries are unorder unless you specified the order. So I'm assuming you are using alphabetical ordering on the emails.

SELECT t.id, t.email, t.state_code FROM ( 

  SELECT id, email, state_code,
  ROW_NUMBER() OVER(partition by id ORDER BY email desc) as cnt
  FROM testdb
  group by id, email, state_code
) as t
WHERE t.cnt <= 2

db fiddle link

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Jocohan

Related Questions