Ankit Khanduri Ankit Khanduri - 3 months ago 10
SQL Question

How can i get incremental counter with sql?

Can you help me with sql query to get the desired result

Database used :- Redshift
requirement is
I have 3 columns as:- dish_id,cateogory_id,counter
So i want counter to increase +1 if the dish_id is repeated and if not it should remain 1
the query i need should be able to query the source table and get the results as

dish_id category_id counter
21 4 1
21 6 2
21 6 3
12 1 1

Answer

Unless I missunderstood your question, you can accomplish that using window functions:

SELECT *,row_number() OVER (PARTITION BY dish_id) FROM my_table;