Need results from a table in a a specific format

I think something like the following should work:
   SELECT Min(Zipcode) As ZipStart, Max(ZipCode) As ZipEnd, Code
   ...
   GROUP BY Code

select [code] , min(zipcode) as zipstart, 0  as zipend
  into #temp
from yourtable
group by [code]

while exists (select [code] from #temp where zipend=0)
begin
  update #temp
     set zipend = x
   from #temp as U inner Join (
   Select t.code,max(a.zipcode) as x
    from #temp as t
   Inner Join yourtable as a
      on t.[code]=a.[code]
     and a.zipcode >= t.zipstart
   Where zipend=0
     and not exists (select [code] from yourtable as z
                       where z.zipcode between t.zipstart and a.zipcode)
    group by t.[code]
    ) as Y
  on u.[code[ = y.[code]
 Where u.zipend=0
 Insert into #temp
   select a.[code] ,min(zipcode) ,0
     from yourtable as a
    inner Join (select [code],max(zipend) as zipend from #temp
                group by [code]) as t  
       on a.[code]=t.[code]
      and a.zipcode > t.zipend
    group by a.[code]
End

select * from #temp
order by zipstart

Please maintain your many very old open questions.  For the record, here are all your open questions:

1 09/25/2003 50 Security Tool  Open Oracle
2 09/25/2003 125 Function based on String Search  Open Oracle
3 09/25/2003 125 Security Admin  Open Oracle
4 06/24/2004 300 Creating new spreadsheets from an old on...  Open Microsoft Excel
5 12/19/2003 250 Using Color Coding  Open Crystal Reports
6 01/08/2004 250 Color Coding in Crystal  Open Crystal Reports
7 05/19/2004 200 Pie Charts in Access  Open Microsoft Access
8 05/21/2004 500 Setting up multiple Windows 2000 server ...  Open Windows 2000
9 07/08/2004 500 Query in SQL  Open Microsoft SQL Server
10 07/11/2004 250 Need results from a table in a  a specif...  Open Microsoft SQL Server
11 07/11/2004 500 Macro to create a new file in a specific...  Open Microsoft Excel

See here for more info:
What happens if I don't close my question?
https://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi78

Thanks.

Unless there is another column in the table to order the input into the SELECT in the proper sequence -- such as datetimeEntered or an identity column -- there is *no* query that will guarantee the results you want.  SQL does *not* guarantee the order of any result without an ORDER BY, so you cannot be sure of getting the rows in a particular order as is required to go from this:
056790000    A101
056800000    A101
056810000    A101
056820000    A101
056890000    A101
056900000    A102
056901111    A102
056901168    A103
056910000    A102
056920000    A102
to this:
056790000   056890000     A101
056900000   056901111     A102
056901168   056901168     A103
056910000   056920000     A102

I was about to write the same thing when I first saw this Scott.

The zipcode column does specify the ordering.

No, it increases then decreases in the original list.  The "order" seems to be "input" order, which by itself SQL does *not* consider when retrieving rows.  It may by chance put rows out in the same order they went in, but that's not assured without an ORDER BY.

I don't follow.  The zip code columns are in ascening order. Which row decreases?

OOPS, never mind, misread one of the numbers.  If the zips do keep ascending, a solution query is possible.

Hi KSelvia,
I tried using this code for my data but it takes a lot of time i have around 50000 rows in my view and the query takes more then two minutes to run.Could it be because the view has the data randomly spread in it..?Actualy the query has been running from 3 minutes now and no results yet......

Thanks

I tried running the inner code(subquery) and that takes more then a minute and does not return any results...
Help!

Ah well yes that makes some sense. We were not much considering table size/performance.  A correlated subquery basically runs for each row in the table so you will run 50,000 subqueries.  Even with good indexes that could take some time.

Try my earlier soloution using temp tables. It will only require 5 passes through the data (rather than 50,000)

i ran the script your wrote Kselvia and it does run but i am posting some data here which ws in the result that seemed wrong to me ...

Zipmin            Zipmax           code
840010000     841021599      A302
840010000     846230000      A301

I havent changed my dataset i was going to test it witha different data but should my zipmin for second row be 841021599. Or it is beng shown this way becasue the code is the same for both and if their was a different code it would have the range start(minzip) change.....
Do you see what i am saying...May be i am just confused looking at t lot f data...
But again the query from Jeff did not run may be we are missing something ...

Sorry about the above data i mistyped it ...
It looks like this ...

Zipmin            Zipmax           code
840010000     841021599      A302
840010000     846230000      A302

i am raising the point value too as you guys have been putting in a lot of effort in to this..

It is possible that is correct based on your input data.

I would have to look at the original data to know.  Try LowFat's solution to see if it is any different.

select * from mytable where zip between '840010000' and '846230000'

How many rows does that return?

select * into #justa302 from mytable where code = 'A302'

then run any of the solutions provided on the table #justa302 and see what is output.

Lowfats solution has been running for 3 minuts now with no results...i think becasue of the data set it takes a lot of time to run the subqueries and that is what is happening out here

has it finished yet?

just noticed you say your view....

is it a view ...
how complex is it...
post the view definition  ..

Nope buddy. It kept on running for like 7 minutes and then i had to kill it. My view defintion is pretty simple i am just selecting the zip and the code form the master table.Nothing complex involved.

More help needed from you guys..thanks..

For me to help I would need you to run the queries I suggested before:

   select * from mytable where zip between '840010000' and '846230000'

   How many rows does that return?

   select * into #justa302 from mytable where code = 'A302'

   then run any of the solutions provided on the table #justa302 and see what is output.

The two temp table solution will be the fastest solution providing it can be fixed, and without knowing why it does not work (see the data that does not conform to the assumptions, and/or change the code to handle it)  I don't know how how to help.

i will have the data posted soon Kselvia, just got stuck in some other problem ... thanks a lot guys ..I think the temp table is the best solution(I am assuming that Kselvias code is corrrect or can be corrected if errors found in result) ..I need to know one more thing from that. Like i said the data is in asecenidng order but their can be gaps ie the zip codes that do not exist or get assigned becasue they do not exist in the database...Can we generate a temp table to find the gaps between zipcodes too ...?

Thanks guys i appreciate ur help

There are ways to find gaps. Do you have, or can you get a list of all actual zip codes?

I wont be possible to get a list of all the zip codes. I can just quote examples

zip      code
965490000      A206
965510000      A206
921580000      A206
965480000      A206
597190000      A302
597330000      A302
597320000      A302
597310000      A302
597300000      A302
597290000      A302
597270000      A302
597240000      A302
597220000      A302
597710000      A302
597200000      A302
597390000      A302
597180000      A302
597170000      A302
597160000      A302
597150000      A302
597140000      A302
597130000      A302
597110000      A302
597100000      A302
597210000      A302
597500000      A302
597620000      A302
597610000      A302
597600000      A302
597590000      A302
597580000      A302
597560000      A302
597550000      A302
597540000      A302
597350000      A302
597510000      A302
597360000      A302
597490000      A302
597480000      A302
597470000      A302
597460000      A302
597450000      A302
597430000      A302
597410000      A302
597400000      A302
597020000      A302
597520000      A302
594500000      A302
597070000      A302
594620000      A302
594610000      A302
594600000      A302
594570000      A302
594560000      A302
594540000      A302
594530000      A302
594640000      A302
594510000      A302
594650000      A302
594480000      A302
594470000      A302
594460000      A302
594450000      A302
594440000      A302
594430000      A302
594420000      A302
594410000      A302
594400000      A302
594520000      A302
594790000      A302
597280000      A302
597010000      A302
594890000      A302
594870000      A302
594860000      A302
594850000      A302
594840000      A302
594830000      A302
594630000      A302
594800000      A302
597030000      A302
594770000      A302
594740000      A302
594730000      A302
594720000      A302
594710000      A302
594690000      A302
594680000      A302
594670000      A302
594660000      A302
594820000      A302
598750000      A302
599290000      A302
599300000      A302
599310000      A302
599320000      A302
599330000      A302
599340000      A302
599350000      A302
599360000      A302
599020000      A302
599010000      A302
599260000      A302
598740000      A302
598730000      A302
598720000      A302
598670000      A302
598710000      A302
598700000      A302
598640000      A302
598630000      A302
599370000      A302
599180000      A302
599030000      A302
599040000      A302
599100000      A302
599110000      A302
599120000      A302
599130000      A302
599140000      A302
599150000      A302
599280000      A302
599170000      A302
599270000      A302
599190000      A302
597250000      A302
599200000      A302
597720000      A302
599210000      A302
599220000      A302
599230000      A302
599250000      A302
598580000      A302
599160000      A302
598200000      A302
598600000      A302
598300000      A302
598290000      A302
598280000      A302
598270000      A302
598260000      A302
598250000      A302
598240000      A302
598320000      A302
598210000      A302
598330000      A302
598120000      A302
598080000      A302
598070000      A302
598060000      A302
598040000      A302
598030000      A302
598020000      A302
598010000      A302
597730000      A302
598230000      A302
598440000      A302
598650000      A302
598560000      A302
598550000      A302
598540000      A302
598530000      A302
598510000      A302
598480000      A302
598470000      A302
598310000      A302
598450000      A302
598590000      A302
598430000      A302
598680000      A302
598420000      A302
598660000      A302
598410000      A302
598400000      A302
598370000      A302
598350000      A302
598340000      A302
598460000      A302
841021469      A302
841021472      A302
841021473      A302
841021470      A302
841021468      A302
841021467      A302
841021466      A302
841021465      A302
841021464      A302
841021463      A302
841021471      A302
841021476      A302
841021475      A302
841021477      A302
841021478      A302
841021483      A302
841021479      A302
841021482      A302
841021462      A302
841021481      A302
841021448      A302
841021480      A302
841021474      A302
845320000      A302
845390000      A302
845370000      A302
845330000      A302
845360000      A302
845350000      A302
845340000      A302
845310000      A302
845250000      A302
590820000      A303
590810000      A303
590790000      A303
590780000      A303
590770000      A303
590750000      A303
592140000      A303
590740000      A303
590730000      A303
590760000      A303

Look at this data may be this will help you get an idea...delemit it before A thats where zip ends

Thanks

more sample

zip      code
005010000      D203
005440000      D203
010010000      D104
010020000      D104
010030000      D104
010040000      D104
010050000      D104
010060000      D104
010070000      D104
013750000      D104
013760000      D104
013780000      D103
013790000      D104
013800000      D104
014200000      D105
014300000      D105
014310000      D105
014320000      D105
014360000      D105

I think this would def initely help

KSelvia,
Anyone...
Can you guys help?

I'm not sure I understand the problem.  The sample data you provided would generate the following:

zmin      zmax      code
--------- --------- ----
005010000 005440000 D203
010010000 013760000 D104
013780000 013780000 D103
013790000 013800000 D104
014200000 014360000 D105
590730000 592140000 A303
594400000 845390000 A302
921580000 965510000 A206

That looks good to me. Show me what about it is incorrect.  (Or better yet, show me how you would like it to look)
Nothing in it exhibits the characteristics of the example you posted

Zipmin          Zipmax             code
840010000     841021599      A302
840010000     846230000      A302

Where ztart zip codes ranges are duplicted.

Thanks KSelvia,
It does work and i was trying to move one steo and do a couple more things with the data if we can....i dont know if it possible but is another interesting thing we can do with this data.
When we get the result we should create another table may be #suggestedcode.
This table looks at the results form the first query i.e data in ranges. now it looks for the ranges and see if the tow consequtve ranges re assigned to same code and if there is any gap between them the gap should be assigned to that Code.

Eg.
This scenario does not exist in the above example but i can have case where range would be
Zmin                  Zmax       Code                  
0917890000   091850000   A102
0917896000   091880000   A102

select * from #Suggestedcode

Zmin                Zmax              Sug_Code
091785000      091789600         A102

basically this is the gap between ranges and i want to assign a code based on whats above it and below it(when the data is arranged in zmin order) so if the above and below code is the same it can be suggested that it belongs to that code..Of course we have a lot of exceptions in the data but if it does not find same code for the row above and below when the data is arranged in order of zmin then it should not suggest a code......ie identify it but not suggest a code for the gap.....
Do I make sense..?

I will try to find the exact data for the above where i saw this but till then we can work on the above problem..ie assigning suggested code

Zipmin            Zipmax           code
840010000     841021599      A302
840010000     846230000      A302

One more question guys how can i raise the point value here because a lot of new questions are coming up from here i want to do that too...
Thanks a Ton Kselvia...

I don't think your example can happen.  The deffinition of the problem would imply those two ranges would already be combined as a single range if there were no intervening codes between them.

Show me some input data that will generate that condition.  

To award more points create a new question with the title "Points for Kselvia"

Thanks Kselvia, I will test it..I am giving you points at tanother place but i will keep this open where i might add a couple of more things..
thanks..

Hi Kselvia,
When finding the gaps the results are not coming accurate ..I need to just find the gaps in the ranges independent of Code so if you understood that i have to find the gap with reference to ranges that yields wrong results....
My output shoould just have gaps i.e the zip codes that do not exist in that table. Once that is identified then i want to move ahead to the next step wchich i will post after we solve this ...
zmin      zmax      code
--------- --------- ----
000000000 005009999 GAPS
005440001 010009999 GAPS
013760001 013779999 GAPS
013780001 013789999 GAPS  <------- the gap starts from 013780001  013789999
013800001 014199999 GAPS  <------- the gap starts again from 013780001
014360001 590729999 GAPS
592140001 594399999 GAPS
845390001 921579999 GAPS
965510001 999999999 GAPS

The result i should have should be independent of the Cose  which i think you are considering ..

Example Again...

zmin           zmax                  code
005010000        005440000              D203
010010000  013670000       D104
013680000  013680000       D103
013700000  013760000       D104
013780000  013780000       D103
013790000        013800000       D104
014200000        015350000      D105
015360000        015460000       D106
015500000        015500000      D104
015600000        015610000              D105

The gaps should be
000000000 - 005010000  GAP
005440000 - 010010000  GAP

At this point i need the output like this