none
partition by without sorting groupings?

    Question

  • Hi,
    I have a problem where I have to use a function like the row_number() function but when I do my groupings I don't want to reorder the whole table.

    So the result I want is in the sequence column of the table below:

    sites signal date sequence
    site1 alarm 2014-05-14 12:00 1
    site1 alarm 2014-05-14 12:30 2
    site1 noalarm 2014-05-14 13:00 1
    site1 noalarm 2014-05-14 13:30 2
    site1 noalarm 2014-05-14 14:00 3
    site1 alarm 2014-05-14 14:30 1
    site1 alarm 2014-05-14 15:00 2
    site2 alarm 2014-05-14 12:00 3
    site2 noalarm 2014-05-14 12:30 1
    site2 alarm 2014-05-14 13:00 1
    site2 noalarm 2014-05-14 13:30 1
    site2 noalarm 2014-05-14 14:00 2



    So to desribe what I am trying to do,I want to group by sites order by date. Then I want to sequence the signals, retaining their date ordering.

    How can I do this?
    Thanks

    Friday, May 16, 2014 8:35 AM

Answers

  • We first need to number on sites and dates with and without a partition. The difference betweens this numbering is constant as long as the level does not change. Then we can number once more, partitioning on this difference:

    ; WITH first_numbering AS (
        SELECT sites, signal, date,
               totalrowno = row_number() OVER(ORDER BY sites, date),
               levelrowno = row_number() OVER(PARTITION BY signal
                                              ORDER BY sites, date)
        FROM   myTable2
    )
    SELECT sites, signal, date,
           sequence = row_number() OVER(PARTITION BY totalrowno - levelrowno
                                        ORDER BY sites, date)
    FROM   first_numbering
    ORDER BY sites, date
    go
    DROP TABLE myTable2


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by peego Friday, May 16, 2014 11:54 AM
    Friday, May 16, 2014 11:25 AM

All replies

  • i think this would be enough

    SELECT sites,signal,[date],Seq
    FROM
    (
    SELECT ROW_NUMBER() OVER (PARTITION BY sites,signal ORDER BY [date]) AS Seq,*
    FROM Table
    )t
    ORDER BY sites,[date]


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Friday, May 16, 2014 8:46 AM
  • Thanks, however, if I try that the sequence doesn't 'reset' so I get the result below:

    sites signal date Seq
    site1 alarm 14/05/2014 12:00 1
    site1 alarm 14/05/2014 12:30 2
    site1 noalarm 14/05/2014 13:00 1
    site1 noalarm 14/05/2014 13:30 2
    site1 noalarm 14/05/2014 14:00 3
    site1 alarm 14/05/2014 14:30 3
    site1 alarm 14/05/2014 15:00 4
    site2 alarm 14/05/2014 12:00 1
    site2 noalarm 14/05/2014 12:30 1
    site2 alarm 14/05/2014 13:00 2
    site2 noalarm 14/05/2014 13:30 2
    site2 noalarm 14/05/2014 14:00 3

    Here's the create data if that's useful.

    Thanks

    create table myTable2 (
    sites varchar(50) NULL,
    signal varchar(50) NULL,
    [date] datetime)

    insert into myTable2(
    sites,
    signal,
    [date]
    )

    values 
    ('site1','alarm','2014-05-14 12:00:00.000'),
    ('site1','alarm','2014-05-14 12:30:00.000'),
    ('site1','noalarm','2014-05-14 13:00:00.000'),
    ('site1','noalarm','2014-05-14 13:30:00.000'),
    ('site1','noalarm','2014-05-14 14:00:000'),
    ('site1','alarm','2014-05-14 14:30:000'),
    ('site1','alarm','2014-05-14 15:00:000'),
    ('site2','alarm','2014-05-14 12:00:000'),
    ('site2','noalarm','2014-05-14 12:30:000'),
    ('site2','alarm','2014-05-14 13:00:000'),
    ('site2','noalarm','2014-05-14 13:30:000'),
    ('site2','noalarm','2014-05-14 14:00:000')




    • Edited by peego Friday, May 16, 2014 9:07 AM
    Friday, May 16, 2014 9:05 AM
  • We first need to number on sites and dates with and without a partition. The difference betweens this numbering is constant as long as the level does not change. Then we can number once more, partitioning on this difference:

    ; WITH first_numbering AS (
        SELECT sites, signal, date,
               totalrowno = row_number() OVER(ORDER BY sites, date),
               levelrowno = row_number() OVER(PARTITION BY signal
                                              ORDER BY sites, date)
        FROM   myTable2
    )
    SELECT sites, signal, date,
           sequence = row_number() OVER(PARTITION BY totalrowno - levelrowno
                                        ORDER BY sites, date)
    FROM   first_numbering
    ORDER BY sites, date
    go
    DROP TABLE myTable2


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by peego Friday, May 16, 2014 11:54 AM
    Friday, May 16, 2014 11:25 AM