postgres assgining same numbers to multiple rows

Question

I have a database table with address information like below.

address 1 　　　 | address 2　　|　　city 　　　　|　state 　|　location id
123 street name　| bldg 1234　　|　Houston　　　|　TX　　|　12345
123 street name　| bldg 1234　　|　Houston　　　|　TX　　|　67890
456 street name　| bldg 7890　　|　New Orleans　|　LA　　|　56789
789 street name　| bldg 0121　　|　long beach　　|　CA　　　|　98765
789 street name　| bldg 0121　　|　long beach　　|　CA　　　|　53218
789 street name　| bldg 0121　　|　long beach　　|　CA　　　|　13579

So even though locations have same address, they all have different ID. What I am trying to do is add a new column called 'group_number' which will group all location id that have same address.

This is my expected outcome.

Group Number | address 1 　　　 | address 2　　|　　city 　　　　|　state 　|　location id
1　　　　　　 | 123 street name　| bldg 1234　　|　Houston　　　|　TX　　|　12345
1　　　　　　 | 123 street name　| bldg 1234　　|　Houston　　　|　TX　　|　67890
2　　　　　　 | 456 street name　| bldg 7890　　|　New Orleans　|　LA　　|　56789
3　　　　　　 | 789 street name　| bldg 0121　　|　long beach　　|　CA　　　|　98765
3　　　　　　 | 789 street name　| bldg 0121　　|　long beach　　|　CA　　　|　53218
3　　　　　　 | 789 street name　| bldg 0121　　|　long beach　　|　CA　　　|　13579

I tried use windows functions like rank(), row_num() and dense_rank() and partition by address1||city||state

I thought that those functions may allow you to have same ranks in each partition. But all those functions assign ranking/sequential row numbers by partition.

Can you help me figure out the best function to create group numbers?

Thanks

Gordon Linoff · Accepted Answer · 2014-09-26 22:36:56Z

5

I think you want dense_rank() used like this:

select dense_rank() over (order by address1, address2, city, state) as GroupNumber,
       address1, address2, city, state, locationid
from addresses a;

You don't need the partition by at all for what you want to do.

answered Sep 26, 2014 at 22:36

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2747356 Over a year ago

This still doesn't work. Event with the same address and other information this gives me a different rank number.

Gordon Linoff Over a year ago

@user2747356 . . . This should give the same id if all four fields are the same. Perhaps there are subtle differences in the values in each row.

Collectives™ on Stack Overflow

postgres assgining same numbers to multiple rows

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related