Hey all;

I'm having a tough time getting my head around the following issues. I have a table with over 67 millions rows of data based upon Zipcodes + 4; i.e. 5-digits zipcode in one column and 4-digit suffix in another; for the entire us.

I need to range the sucker to reduce file sizes for export files. My problem is ranging the zipcode based upon the 4-digit +4 column.

I am using SQL Server 2005; I have tried the all the sorting and grouping options I know of along with DISTINCT MIN and MAX to no avail.

Everytime the 4-digit +4 changes cities (i.e. CITY_NAME) I need to group that as a specific range; if the same city picks up after that I need that to be a separate range. Here's the input and output;

Input:
90001 1125 06037 0624477 FLORENCE-GRAHAM
90001 1126 06037 0624477 FLORENCE-GRAHAM
90001 1127 06037 0644000 LOS ANGELES
90001 1128 06037 0644000 LOS ANGELES
90001 1129 06037 0624477 FLORENCE-GRAHAM
90001 1130 06037 0644000 LOS ANGELES
90001 1131 06037 0624477 FLORENCE-GRAHAM
90001 1132 06037 0644000 LOS ANGELES
90001 1133 06037 0624477 FLORENCE-GRAHAM
90001 1134 06037 0624477 FLORENCE-GRAHAM

Output:
90001 0001 1000 FLORENCE-GRAHAM 1
90001 1001 1098 LOS ANGELES 2
90001 1100 1126 FLORENCE-GRAHAM 3
90001 1127 1128 LOS ANGELES 4
90001 1129 1129 FLORENCE-GRAHAM 5
90001 1130 1130 LOS ANGELES 6
90001 1131 1131 FLORENCE-GRAHAM 7
90001 1132 1132 LOS ANGELES 8
90001 1133 1166 FLORENCE-GRAHAM 9
90001 1167 1168 LOS ANGELES 10

This is just a sample dataset I manipulated for 1 zipcode; dude I can't do that for the entire US!

Any help is appreciated

Mark