How to get groups of sequence numbers? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to get groups of sequence numbers?

Author

Topic

saman0suke
Starting Member

6 Posts

Posted - 2011-03-01 : 16:59:32

I have a problem, I'm trying to do the following:

I have numbers of data on friday, in this order:

1
2
3
4
6
7
8

As you may notice, the number five is missing, so that means document number five was not created on friday, I need to create a query where I can recognize the missing document and present the info like this:

friday : 1 - 4
friday : 6 - 8

To display a range of data created, could you help me out please? thanks!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-01 : 17:09:01

Proof of concept:

declare @a table(i int not null, d datetime not null default (getdate()))
insert @a(i) values(1)
insert @a(i) values(2)
insert @a(i) values(3)
insert @a(i) values(4)
insert @a(i) values(6)
insert @a(i) values(7)
insert @a(i) values(8)

;with a(i,d,r) as 
	(select *, row_number() over (partition by d order by i) from @a)
select d, cast(min(i) as varchar) + '-' + cast(max(i) as varchar) 
from a
group by d, r-i
order by 2

saman0suke
Starting Member

6 Posts

Posted - 2011-03-01 : 18:20:21

I just tried it, but it does the following:

07/01/2011 0-0
07/01/2011 1-1
07/01/2011 2-2
07/01/2011 3-3
07/01/2011 4-4
07/01/2011 6-6
07/01/2011 7-7
07/01/2011 8-8

Is this correct for the statement? because I'm trying to get something like this:

07/01/2011 1-4
07/01/2011 6-8

Is there any way to do it without using Common table expressions? first time I've actually tried to use them, thanks!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-01 : 18:28:36

I got the results you requested from the proof of concept code. Post the query you used and the actual data.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-01 : 18:37:06

You can do it without CTEs but you'd have to move the ROW_NUMBER() calculation elsewhere, and it will probably be more complicated than having it in the CTE. You can't put it in the GROUP BY.

saman0suke
Starting Member

6 Posts

Posted - 2011-03-01 : 18:39:17

ok ok, here it goes:
table: documents_table
fields:
num_field = nvarchar
date_field = datetime

with a(i,d,r) as
(select num_field, date_field, row_number() over (partition by num_field order by date_field) from documents_table)
select d, cast(min(i) as varchar) + '-' + cast(max(i) as varchar)
from a
group by d, r-i
order by 2

Maybe I missed something, as I said, first time working on CTE, thanks for the help!

saman0suke
Starting Member

6 Posts

Posted - 2011-03-01 : 18:48:56

IT WORKED!!! I was doing something wrong, the DATETIME value include minutes, seconds, and miliseconds if I'm not mistaken, so, since I have the same datetime, but different minutes, etc, it was not doing the GROUP BY properly, I just applied a CONVERT to varchar to the date and now works!!

Now, since this is the first time I encountered a CTE statement, could you give me a brief explanation about how the code works? I would really appreciate it in order to understanding it better, thanks!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-01 : 20:11:17

The big problem I had with CTEs was that all the examples I could find dealt with recursion, and recursion obscures what a CTE can do. The best way to think of a CTE (for me anyway) is:

1. It's just like a subquery (but better)
2. Except it's defined before the rest of the query elements
3. And it can be referenced multiple times from a single definition

In the example I provided, all the CTE does is provide a shell where I can generate row numbers one time, and then reference it later in another query. I could have done the same thing with a temp table or table variable and an identity column, but the CTE lets me do that without having to store any actual data. I could also have written it as:

select d, cast(min(i) as varchar) + '-' + cast(max(i) as varchar)
from (select num_field as i, date_field as d, row_number() over (partition by num_field order by date_field) as r from documents_table) a
group by d, r-i
order by 2

For something as simple as this, either technique will work, it's just a personal preference which one to use.

saman0suke
Starting Member

6 Posts

Posted - 2011-03-01 : 22:23:49

I think I got the idea, but I'm still a little bit confused with the query, for example:

1.Why do you GROUP BY this operation(r - i)
2.What exactly does the ROW_NUMBER() OVER(PARTITION......)
3. I've never used PARTITION before, how does it work?

Thanks, I'm not a complete noobie since I work on this, but never with CTE and PARTITION, thanks! I always learn something new everyday.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-02 : 06:40:31

PARTITION is part of the ROW_NUMBER() function, it's basically another way to do GROUP BY but within the function itself.

GROUP BY r-i says: If the IDs increase sequentially, then subtracting them from the row number will yield the same result (sequence). If there's a gap, they'll start a new sequence with a new value. GROUPing on this sequence number ties each ID to its sequence, and lets us find the MIN and MAX.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources