CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
PorkChopSandwiches
PorkChopSandwiches

Reputation: 43

Create a range from consecutive zip codes

I have a zone table with starting and ending zip codes, but it was loaded with each zip code individually. I now need to create a report that displays the zips in the assigned zones, but it is returning a bunch of records. What I want to do is create a range from this list of consecutive zips.

My table contains the following

FromZip ToZip
07543   07543
07604   07604
07509   07509
07510   07510
07511   07511
07512   07512
07513   07513
07514   07514

I would like to return

FromZip ToZip
07543   07543
07604   07604
07509   07514

Upvotes: 1

Views: 179

Answers (1)

DhruvJoshi
DhruvJoshi

Reputation: 17146

Please try a query like below

select 
  MIN(FromZip) as FromZip, 
  MAX(ToZip) as ToZip
from 
 (
  Select 
   *, 
   ROW_NUMBER() OVER(ORDER BY ToZip asc) as ranking 
   from tbl
 ) t  
 group by 
   (CAST(ToZip AS INT)-Ranking)
 order by 
   (CAST(ToZip AS INT)-Ranking)

Live SQL demo here

Upvotes: 1

Related Questions