How to get id for group by?

user_15360018 0
Viewed 861

I have sql script for grouping values

select count(*), equity_name, date, field
from balance
group by equity_name, date, field
having count(*)>1

How can i get ids for each group? In future i need to insert dublicates values to another table.

My current result:

cur_res

Result what i need:

result

postgresql sql
1 Answers

If you want to assign a sequential number to the groups, you can use:

select count(*), equity_name, date, field,
       row_number() over (order by equity_name, date, field) as id
from balance
group by equity_name, date, field
having count(*) > 1;

If you want a stable "name" for the group that doesn't depend on the data in the table, you can just concatenate the fields together:

select count(*), equity_name, date, field,
       concat_ws(':', equity_name, date, field) as id
from balance
group by equity_name, date, field
having count(*) > 1;

However, if you really care about persisting the group over time, I would suggest that you save the results into a table with an auto-generated identity column.

user_11440350
Related