# 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

• 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 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]```

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 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 Friday, May 16, 2014 11:54 AM
Friday, May 16, 2014 11:25 AM