Post reply

need some help with row_number()

  • August 3, 2010 at 7:12 am

    #225642

    I'm drawing a deep blank today;

    I have data that contains an area.

    each area may have one or more buildings.

    each building can have one or more apartments.

    I need to index both the buildings to an area, and the apartments to a building, coming up with row_number() values instead of the key fromt he original data...simple, right?

    not for me today apparently.

    in the example below, area "409" has 36 buildings, each with one apartment.

    area "1887" has one building, but 16 apartments.

    I've confused myself, becuase i'm not getting expected data.

    in my code i'm trying, i cannot get area "1887" to have a "buildingindex" of 1 for all 16 apartments; but area "409" has a building index the way i'd expect...an incrmenting integer, one for each building.

    here's my current example code:

    SELECT

    [AREAID] AS ID,

    ROW_NUMBER() OVER (PARTITION BY [AREAID] ORDER BY [AREAID],[BUILDINGID]) AS BUILDINGINDEX,

    ROW_NUMBER() OVER (PARTITION BY [AREAID],[BUILDINGID] ORDER BY [AREAID],[BUILDINGID]) AS APARTMENTINDEX,

    ROW_NUMBER() OVER (PARTITION BY [AREAID],[BUILDINGID],[APARTMENTID] ORDER BY [AREAID],[BUILDINGID]) AS TENANTINDEX,

    #TEMP.*

    FROM #TEMP

    here's setup data:

    CREATE TABLE [dbo].[#TEMP] (

    [AREAID] INT NULL,

    [BUILDINGID] VARCHAR(15) NULL,

    [APARTMENTID] VARCHAR(15) NULL)

    INSERT INTO #TEMP

    SELECT 'AREAID','BUILDINGID','APARTMENTID' UNION ALL

    SELECT '409','56100116001','2000100441' UNION ALL

    SELECT '409','56100116002','2000100442' UNION ALL

    SELECT '409','56100116003','2000100443' UNION ALL

    SELECT '409','56100116004','2000100444' UNION ALL

    SELECT '409','56100116005','2000100445' UNION ALL

    SELECT '409','56100116006','2000100446' UNION ALL

    SELECT '409','56100116007','2000100462' UNION ALL

    SELECT '409','56100116008','2000100463' UNION ALL

    SELECT '409','56100116009','2000100464' UNION ALL

    SELECT '409','56100116010','2000100431' UNION ALL

    SELECT '409','56100116011','2000100447' UNION ALL

    SELECT '409','56100116012','2000100448' UNION ALL

    SELECT '409','56100116013','2000100449' UNION ALL

    SELECT '409','56100116014','2000100450' UNION ALL

    SELECT '409','56100116015','2000100451' UNION ALL

    SELECT '409','56100116016','2000100452' UNION ALL

    SELECT '409','56100116017','2000100453' UNION ALL

    SELECT '409','56100116018','2000100454' UNION ALL

    SELECT '409','56100116019','2000100455' UNION ALL

    SELECT '409','56100116020','2000100456' UNION ALL

    SELECT '409','56100116021','2000100457' UNION ALL

    SELECT '409','56100116022','2000100458' UNION ALL

    SELECT '409','56100116023','2000100459' UNION ALL

    SELECT '409','56100116024','2000100460' UNION ALL

    SELECT '409','56100116025','2000100461' UNION ALL

    SELECT '409','56100116026','2000100430' UNION ALL

    SELECT '409','56100116027','2000100465' UNION ALL

    SELECT '409','56100116028','2000100432' UNION ALL

    SELECT '409','56100116029','2000100433' UNION ALL

    SELECT '409','56100116030','2000100434' UNION ALL

    SELECT '409','56100116031','2000100435' UNION ALL

    SELECT '409','56100116032','2000100436' UNION ALL

    SELECT '409','56100116033','2000100437' UNION ALL

    SELECT '409','56100116034','2000100438' UNION ALL

    SELECT '409','56100116035','2000100439' UNION ALL

    SELECT '409','56100116036','2000100440' UNION ALL

    SELECT '1887','56100039001','2000100196' UNION ALL

    SELECT '1887','56100039001','2000100197' UNION ALL

    SELECT '1887','56100039001','2000100198' UNION ALL

    SELECT '1887','56100039001','2000100199' UNION ALL

    SELECT '1887','56100039001','2000100200' UNION ALL

    SELECT '1887','56100039001','2000100201' UNION ALL

    SELECT '1887','56100039001','2000100202' UNION ALL

    SELECT '1887','56100039001','2000100203' UNION ALL

    SELECT '1887','56100039001','2000100204' UNION ALL

    SELECT '1887','56100039001','2000100205' UNION ALL

    SELECT '1887','56100039001','2000100206' UNION ALL

    SELECT '1887','56100039001','2000100207' UNION ALL

    SELECT '1887','56100039001','2000100208' UNION ALL

    SELECT '1887','56100039001','2000100209' UNION ALL

    SELECT '1887','56100039001','2000100210' UNION ALL

    SELECT '1887','56100039001','2000100211'

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 3, 2010 at 7:19 am

    #1201498

    here's the visual results of my current query for reference: my expectation is that for AREAID 1887, the "buidlingindex" should be all "1", and the apartmentindex increments as seen below.

    ID BUILDINGINDEX APARTMENTINDEX TENANTINDEX AREAID BUILDINGID APARTMENTID

    ----- -------------- -------------- ----------- ----------------------------------

    409 1 1 1 409 56100116001 2000100441

    409 2 1 1 409 56100116002 2000100442

    409 3 1 1 409 56100116003 2000100443

    409 4 1 1 409 56100116004 2000100444

    409 5 1 1 409 56100116005 2000100445

    409 6 1 1 409 56100116006 2000100446

    409 7 1 1 409 56100116007 2000100462

    409 8 1 1 409 56100116008 2000100463

    409 9 1 1 409 56100116009 2000100464

    409 10 1 1 409 56100116010 2000100431

    409 11 1 1 409 56100116011 2000100447

    409 12 1 1 409 56100116012 2000100448

    409 13 1 1 409 56100116013 2000100449

    409 14 1 1 409 56100116014 2000100450

    409 15 1 1 409 56100116015 2000100451

    409 16 1 1 409 56100116016 2000100452

    409 17 1 1 409 56100116017 2000100453

    409 18 1 1 409 56100116018 2000100454

    409 19 1 1 409 56100116019 2000100455

    409 20 1 1 409 56100116020 2000100456

    409 21 1 1 409 56100116021 2000100457

    409 22 1 1 409 56100116022 2000100458

    409 23 1 1 409 56100116023 2000100459

    409 24 1 1 409 56100116024 2000100460

    409 25 1 1 409 56100116025 2000100461

    409 26 1 1 409 56100116026 2000100430

    409 27 1 1 409 56100116027 2000100465

    409 28 1 1 409 56100116028 2000100432

    409 29 1 1 409 56100116029 2000100433

    409 30 1 1 409 56100116030 2000100434

    409 31 1 1 409 56100116031 2000100435

    409 32 1 1 409 56100116032 2000100436

    409 33 1 1 409 56100116033 2000100437

    409 34 1 1 409 56100116034 2000100438

    409 35 1 1 409 56100116035 2000100439

    409 36 1 1 409 56100116036 2000100440

    1887 1 1 1 1887 56100039001 2000100196

    1887 2 2 1 1887 56100039001 2000100197

    1887 3 3 1 1887 56100039001 2000100198

    1887 4 4 1 1887 56100039001 2000100199

    1887 5 5 1 1887 56100039001 2000100200

    1887 6 6 1 1887 56100039001 2000100201

    1887 7 7 1 1887 56100039001 2000100202

    1887 8 8 1 1887 56100039001 2000100203

    1887 9 9 1 1887 56100039001 2000100204

    1887 10 10 1 1887 56100039001 2000100205

    1887 11 11 1 1887 56100039001 2000100206

    1887 12 12 1 1887 56100039001 2000100207

    1887 13 13 1 1887 56100039001 2000100208

    1887 14 14 1 1887 56100039001 2000100209

    1887 15 15 1 1887 56100039001 2000100210

    1887 16 16 1 1887 56100039001 2000100211

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 3, 2010 at 7:55 am

    #1201537

    DENSE_RANK() OVER (PARTITION BY [AREAID] ORDER BY [BUILDINGID]) AS BUILDINGINDEX,

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply