CODEWITHSUNDEEP
sqlpostgresql
Aamir Khan
Aamir Khan

Reputation: 141

Add a number for duplicate values in posgresql

I've a query in MySQL and I'm looking for a query which can perform below operation using Posgres

MySQL Query :

update APT_ADHOC_DISH_SRC_TABLE_AAMIR_TCH3 a,
(
  SELECT @row_number:=CASE WHEN @email=email THEN @row_number+1 ELSE 1 END AS row_number,
    @email:=email AS email,id  
  FROM APT_ADHOC_DISH_SRC_TABLE_AAMIR_TCH3, 
  (SELECT @row_number:=0,@email:='') AS t  
  ORDER BY email
) b 
set a.r_no=b.row_number where a.id=b.id

Output:

Email Row Number
Aamir 1
Aamir 2
Aamir 3
Suresh 1
Suresh 2
Hafiz 1

Upvotes: 1

Views: 39

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562721

WITH cte AS (
      SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS row_number
      FROM APT_ADHOC_DISH_SRC_TABLE_AAMIR_TCH3
    )
UPDATE APT_ADHOC_DISH_SRC_TABLE_AAMIR_TCH3
SET row_number = cte.row_number
FROM cte
WHERE APT_ADHOC_DISH_SRC_TABLE_AAMIR_TCH3.id = cte.id

https://www.db-fiddle.com/f/3RqNHRGFjkU74v33upyfhi/0

Upvotes: 2

Related Questions