locked
[2000] group contiguous rows RRS feed

  • Question

  • EDIT : added a line at the end of the sample data script, to delete some rows. For some reason, I didn't paste it in the initial post

    ----

    Hello,

    I'm using SQL Server 2000.

    I have a table with one date and one row id per row.

    I want to group all rows by date, ordered by row_id.

    Here's some example data:

    drop table data create table data ( record int identity(1, 1), event datetime ) set nocount on insert data (event) values ( '01/01/2012') insert data (event) values ( '01/01/2012') insert data (event) values ( '01/01/2012') insert data (event) values ( '01/01/2012') insert data (event) values ( '01/01/2012') insert data (event) values ( '01/01/2012') insert data (event) values ( '01/01/2012') insert data (event) values ( '03/01/2012') insert data (event) values ( '03/01/2012') insert data (event) values ( '01/01/2012') insert data (event) values ( '01/01/2012') insert data (event) values ( '01/01/2012') insert data (event) values ( '09/01/2012') insert data (event) values ( '01/01/2012') insert data (event) values ( '01/01/2012') insert data (event) values ( '01/01/2012') insert data (event) values ( '02/01/2012') insert data (event) values ( '02/01/2012') insert data (event) values ( '02/01/2012') insert data (event) values ( '02/01/2012') insert data (event) values ( '02/01/2012') insert data (event) values ( '02/01/2012') insert data (event) values ( '02/01/2012') insert data (event) values ( '26/12/2011') insert data (event) values ( '26/12/2011') insert data (event) values ( '26/12/2011') insert data (event) values ( '02/01/2012') insert data (event) values ( '02/01/2012') insert data (event) values ( '02/01/2012')

    delete from data where record in (5, 16, 25)


    I need a query which allows me to see when the date changes, the number of affected rows, and the ids of the affected rows.

    Something like:

    start    end    date        count
    1        7      01/01/2012  6
    8        9      03/01/2012  2
    10       12     01/01/2012  3
    13       13     09/01/2012  1
    14       15     01/01/2012  2
    17       23     02/01/2012  6
    24       26     26/12/2011  2
    27       29     02/01/2012  3

    I have tried to write a query to do that, but I don't know where to go from here.

    drop table pointers
    create table pointers (
        record int,
        event datetime,
        prev_record int,
        prev_date datetime
    )
    
    insert pointers
    select a.* , b.*
    from data a
    inner join data b on b.record = 
        (select max( i.record ) from data i where i.record < a.record ) --previous record
    where b.event > a.event
    
    insert pointers
    select a.* , b.*
    from data a
    inner join data b on b.record = 
        (select min( i.record ) from data i where i.record > a.record ) --next record
    where b.event > a.event

    Can you help me get the results I need?


    if a problem looks too big, break it into smaller objects





    Wednesday, April 11, 2012 10:20 AM

Answers

  • I found a solution to my problem. It doesn't look very pretty, but it's simple to read, doesn't use a loop, and it works.

    drop table data create table data (     record int identity(1, 1),     event datetime )  set nocount on 
    insert data (event) values ( '01/01/2012') insert data (event) values ( '01/01/2012') insert data (event) values ( '01/01/2012') 
    insert data (event) values ( '01/01/2012') insert data (event) values ( '01/01/2012') insert data (event) values ( '01/01/2012') 
    insert data (event) values ( '01/01/2012') insert data (event) values ( '03/01/2012') insert data (event) values ( '03/01/2012') 
    insert data (event) values ( '01/01/2012') insert data (event) values ( '01/01/2012') insert data (event) values ( '01/01/2012') 
    insert data (event) values ( '09/01/2012') insert data (event) values ( '01/01/2012') insert data (event) values ( '01/01/2012') 
    insert data (event) values ( '01/01/2012') insert data (event) values ( '02/01/2012') insert data (event) values ( '02/01/2012') 
    insert data (event) values ( '02/01/2012') insert data (event) values ( '02/01/2012') insert data (event) values ( '02/01/2012') 
    insert data (event) values ( '02/01/2012') insert data (event) values ( '02/01/2012') insert data (event) values ( '26/12/2011') 
    insert data (event) values ( '26/12/2011') insert data (event) values ( '26/12/2011') insert data (event) values ( '02/01/2012') 
    insert data (event) values ( '02/01/2012') insert data (event) values ( '02/01/2012')
    
    delete from data where record in (5, 16, 25)
    
    drop table summary
    create table summary (
        start int,
        endr int,
        [date] datetime
    )
    
    -- insert the first record
    insert summary(start, date)
    select top 1 record, event from data order by record asc
    
    
    -- insert a row whenever the date changes
    insert summary (start, date)
    select b.record, b.event from data a
    left outer join data b on b.record = (select min( i.record ) from data i where i.record > a.record )
    where b.event <> a.event
    
    -- look for the endr value
    update s 
    -- note how I don't look for an existing record. This allows me to have start and endr point to the same record
    set endr = (select top 1 min(record)-1 from data
                where record>start and event <> date)
    from summary s
    
    -- correct the endr column to use an existing record
    update summary set endr = (select max(record) from data where record <= endr)
    
    -- fill the last "endr" record, because it's NULL
    update summary set endr = (select max(record) from data where event = date) where endr is null
    
    -- select * from data
    select * from summary
    
    


    if a problem looks too big, break it into smaller objects

    • Marked as answer by M0nkeyMaster Wednesday, April 11, 2012 1:52 PM
    Wednesday, April 11, 2012 1:52 PM

All replies

  • select
    	min(record) start,
    	max(record) "end",
    	"event" "date",
    	count(*)
    from
    	data
    group by "event"
    order by "event" asc


    Wednesday, April 11, 2012 11:53 AM
  • I found a solution to my problem. It doesn't look very pretty, it doesn't seem to scale awfully well, but at least it doesn't use a loop and it works.

    drop table data create table data (     record int identity(1, 1),     event datetime )  set nocount on 
    insert data (event) values ( '01/01/2012') insert data (event) values ( '01/01/2012') insert data (event) values ( '01/01/2012') insert data (event) values ( '01/01/2012') insert data (event) values ( '01/01/2012') insert data (event) values ( '01/01/2012') insert data (event) values ( '01/01/2012') insert data (event) values ( '03/01/2012') insert data (event) values ( '03/01/2012') insert data (event) values ( '01/01/2012') insert data (event) values ( '01/01/2012') insert data (event) values ( '01/01/2012') insert data (event) values ( '09/01/2012') insert data (event) values ( '01/01/2012') insert data (event) values ( '01/01/2012') insert data (event) values ( '01/01/2012') insert data (event) values ( '02/01/2012') insert data (event) values ( '02/01/2012') insert data (event) values ( '02/01/2012') insert data (event) values ( '02/01/2012') insert data (event) values ( '02/01/2012') insert data (event) values ( '02/01/2012') insert data (event) values ( '02/01/2012') insert data (event) values ( '26/12/2011') insert data (event) values ( '26/12/2011') insert data (event) values ( '26/12/2011') insert data (event) values ( '02/01/2012') insert data (event) values ( '02/01/2012') insert data (event) values ( '02/01/2012')
    delete from data where record in (5, 16, 25)
    
    drop table summary
    create table summary (
        start int,
        endr int,
        [date] datetime
    )
    
    -- insert the first record
    insert summary(start, date)
    select top 1 record, event from data order by record asc
    
    -- insert a row whenever the date changes
    insert summary (start, date)
    select b.record, b.event from data a
    left outer join data b on b.record = (select min( i.record ) from data i where i.record > a.record )
    where b.event <> a.event
    
    -- look for the endr value
    update s 
    -- note how I don't look for an existing record. This allows me to have start and endr point to the same record
    set endr = (select top 1 min(record)-1 from data
                where record>start and event <> date)
    from summary s
    
    -- correct the endr column to use an existing record
    update summary set endr = (select max(record) from data where record <= endr)
    
    -- fill the last "endr" record, because it's NULL
    update summary set endr = (select max(record) from data where event = date) where endr is null
    
    -- select * from data
    select * from summary
    
    


    if a problem looks too big, break it into smaller objects

    Wednesday, April 11, 2012 1:48 PM
  • I found a solution to my problem. It doesn't look very pretty, but it's simple to read, doesn't use a loop, and it works.

    drop table data create table data (     record int identity(1, 1),     event datetime )  set nocount on 
    insert data (event) values ( '01/01/2012') insert data (event) values ( '01/01/2012') insert data (event) values ( '01/01/2012') 
    insert data (event) values ( '01/01/2012') insert data (event) values ( '01/01/2012') insert data (event) values ( '01/01/2012') 
    insert data (event) values ( '01/01/2012') insert data (event) values ( '03/01/2012') insert data (event) values ( '03/01/2012') 
    insert data (event) values ( '01/01/2012') insert data (event) values ( '01/01/2012') insert data (event) values ( '01/01/2012') 
    insert data (event) values ( '09/01/2012') insert data (event) values ( '01/01/2012') insert data (event) values ( '01/01/2012') 
    insert data (event) values ( '01/01/2012') insert data (event) values ( '02/01/2012') insert data (event) values ( '02/01/2012') 
    insert data (event) values ( '02/01/2012') insert data (event) values ( '02/01/2012') insert data (event) values ( '02/01/2012') 
    insert data (event) values ( '02/01/2012') insert data (event) values ( '02/01/2012') insert data (event) values ( '26/12/2011') 
    insert data (event) values ( '26/12/2011') insert data (event) values ( '26/12/2011') insert data (event) values ( '02/01/2012') 
    insert data (event) values ( '02/01/2012') insert data (event) values ( '02/01/2012')
    
    delete from data where record in (5, 16, 25)
    
    drop table summary
    create table summary (
        start int,
        endr int,
        [date] datetime
    )
    
    -- insert the first record
    insert summary(start, date)
    select top 1 record, event from data order by record asc
    
    
    -- insert a row whenever the date changes
    insert summary (start, date)
    select b.record, b.event from data a
    left outer join data b on b.record = (select min( i.record ) from data i where i.record > a.record )
    where b.event <> a.event
    
    -- look for the endr value
    update s 
    -- note how I don't look for an existing record. This allows me to have start and endr point to the same record
    set endr = (select top 1 min(record)-1 from data
                where record>start and event <> date)
    from summary s
    
    -- correct the endr column to use an existing record
    update summary set endr = (select max(record) from data where record <= endr)
    
    -- fill the last "endr" record, because it's NULL
    update summary set endr = (select max(record) from data where event = date) where endr is null
    
    -- select * from data
    select * from summary
    
    


    if a problem looks too big, break it into smaller objects

    • Marked as answer by M0nkeyMaster Wednesday, April 11, 2012 1:52 PM
    Wednesday, April 11, 2012 1:52 PM
  • I believe your problem (finding islands of data) is harder to solve in SQL 2000 than in more recent versions. I asked a similar question before, you may be interested to view this thread

    http://forums.asp.net/p/1417268/3129677.aspx#3129677


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Wednesday, April 11, 2012 5:59 PM