CODEWITHSUNDEEP
sql
Otshepeng Ditshego
Otshepeng Ditshego

Reputation: 197

Index column based on condition

I am trying to create a column that will count or add an integer to a suburb and for the same suburb that have different regions the values should be different. My table looks like this:

Region|suburb
GC    |Brooklyn
ER    |Brooklyn
GC    |Brooklyn
GC    |Mountain View
GC    |Lynnwood

Expected results

   Region |suburb        |int
    GC    |Brooklyn      |1
    ER    |Brooklyn      |2
    GC    |Brooklyn      |1
    GC    |Mountain View |3
    GC    |Lynnwood      |4

Bear in mind there's over a thousand suburbs so it wouldnt be practical to to use a case statement.

Upvotes: 0

Views: 548

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You seem to want rank():

select region, suburb,
       rank() over (partition by suburb order by region) as num
from t;

This enumerates the suburbs in alphabetical order by region. That is not exactly the results in your example, but it is consistent with the question you are asking.

Upvotes: 1

callisto
callisto

Reputation: 5083

What you have here: 

Region|suburb
_______________
GC    |Brooklyn
ER    |Brooklyn
GC    |Brooklyn
GC    |Mountain View
GC    |Lynnwood

Can be normalised to an new table with unique Reqion and suburb fields like so:

Id|Region|Suburb
__________________
1 |GC    |Brooklyn
2 |ER    |Brooklyn
3 |GC    |Mountain View
4 |GC    |Lynnwood

See how the duplicate GC-Brooklyn entry is not there?

Then use this new table's Id in you existing table ( from which you have removed the Region and Suburb field. Like so:

ColumnA |ColumnB |RegionSuburbId
________________________________
Foo1    |Bar1    |3
Ipsum   |Factum  |1
Loony   |Tunes   |4
Acme    |Anvil   |1

Then:

SELECT * FROM OtshepengDitshego
INNER JOIN RegionSuburb on HorseNoName.RegionSuburbId = RegionSuburb.Id

would give you

ColumnA |ColumnB |RegionSuburbId |ID |Region|Suburb
____________________________________________________
Foo1    |Bar1    |3              | 3 |GC    |Mountain View
Ipsum   |Factum  |1              | 1 |GC    |Brooklyn
Loony   |Tunes   |4              | 4 |GC    |Lynnwood
Acme    |Anvil   |1              | 1 |GC    |Brooklyn

Upvotes: 0

Related Questions