Return consecutive numbers - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Return consecutive numbers

Author

Topic

Bell
Starting Member

4 Posts

Posted - 2010-12-17 : 17:18:33

I have 2 columns A, B, and C in a Table X. The length of Table X is 100 rows. Column A has the ‘Apple’ repeating the length of the entire table. Column C has the time. And column B has 4 different numbers.
These numbers appear in a sequence at certain time intervals and appear randomly in others. How do I query so that it returns only the rows with numbers appearing sequentially (i.e., filter out the random numbers)?

Table X

A B C
Apple 3 12/1/2010 11:04 AM
Apple 4 12/1/2010 11:58 AM
Apple 1 12/1/2010 11:00 AM
Apple 1 12/1/2010 11:01 AM
Apple 1 12/1/2010 09:05 AM
Apple 2 12/1/2010 09:10 AM
Apple 3 12/1/2010 09:40 AM
Apple 4 12/1/2010 10:00 AM
Apple 2 12/4/2010 03:25 PM
Apple 1 12/4/2010 12:47 PM
.
.
.
.
.

I want to query Table X such that the first 4 and the last 2 rows don't show up. What I mean is I want only rows that have consecutive numbers in Column B

Thanks much.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-17 : 18:48:08

Can you describe the logic used to determine what should show up. There is no order to the rows you've listed. If one were to use the Date column (C) and try to get consecutive numbers, then the result set would not be as you have described.

Bell
Starting Member

4 Posts

Posted - 2010-12-20 : 12:08:15

What I mean is :

Assume Col A was a system name. Col B has a bunch of ID's associated with system in Col A. These ID's mean something wrt the system. Col C has the timestamp at which these ID's are generated by the system.

This is the query I wrote to obtain the above data:

SELECT Name, ID, time
FROM Xtable
WHERE (Name='Apple') AND ID in ('1', '2', '3', '4')
GROUP BY Name, ID, time

The problem with this query is that it returns rows as long as it contains one of the above numbers (not necessarily in a sequence)

I want to tweak it such that it returns values in all three columns only when the IDs in Column B occur in a sequence (1,2,3,4). I want this information because the sequential IDs represent a set of events that I'm using to extract some information.

Bell
Starting Member

4 Posts

Posted - 2010-12-20 : 12:22:51

Also, Column C cannot be used to get consecutive numbers since there is no definite pattern in the time stamp.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-20 : 13:24:40

Ahh, well the issue is that tables, by definition, do not have any order. So, given the data you have provided there is no reliable way to get the data you want. Is there another column that you did not show that can be used to order the data?

SparkByte
Yak Posting Veteran

60 Posts

Posted - 2010-12-20 : 15:24:41

What version of SQL are you using? If SQL 2005/2008.

Hopefully I am following your logic and this helps.

Create Table #X
(
	SystemName Varchar(10),
	SysID Int,
	SysDateTime DateTime
)

Insert into #X Values('Apple', 3, '12/1/2010 11:04 AM');
Insert into #X Values('Apple', 4, '12/1/2010 11:58 AM');
Insert into #X Values('Apple', 1, '12/1/2010 11:00 AM');
Insert into #X Values('Apple', 1, '12/1/2010 11:01 AM');
Insert into #X Values('Apple', 1, '12/1/2010 09:05 AM');
Insert into #X Values('Apple', 2, '12/1/2010 09:10 AM');
Insert into #X Values('Apple', 3, '12/1/2010 09:40 AM');
Insert into #X Values('Apple', 4, '12/1/2010 10:00 AM');
Insert into #X Values('Apple', 2, '12/4/2010 03:25 PM');
Insert into #X Values('Apple', 1, '12/4/2010 12:47 PM');

SELECT 
	Row_Number() Over (partition by SystemName Order by SysID) As 'PK',
	SystemName, 
	SysID, 
	SysDateTime
FROM #X

Drop Table #X

results:


PK                   SystemName SysID       SysDateTime
-------------------- ---------- ----------- -----------------------
1                    Apple      1           2010-12-01 11:00:00.000
2                    Apple      1           2010-12-01 11:01:00.000
3                    Apple      1           2010-12-01 09:05:00.000
4                    Apple      1           2010-12-04 12:47:00.000
5                    Apple      2           2010-12-04 15:25:00.000
6                    Apple      2           2010-12-01 09:10:00.000
7                    Apple      3           2010-12-01 09:40:00.000
8                    Apple      3           2010-12-01 11:04:00.000
9                    Apple      4           2010-12-01 11:58:00.000
10                   Apple      4           2010-12-01 10:00:00.000

Thank You,

John

SparkByte
Yak Posting Veteran

60 Posts

Posted - 2010-12-20 : 15:28:28

Sorry, You might want to change this
Row_Number() Over (partition by SystemName Order by SysID) As 'PK',
to this
Row_Number() Over (partition by SystemName Order by SysID, SysDateTime) As 'PK',

Thank You,

John

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-20 : 15:48:28

THis is the result set that Bell wants:
Apple 1 12/1/2010 09:05 AM
Apple 2 12/1/2010 09:10 AM
Apple 3 12/1/2010 09:40 AM
Apple 4 12/1/2010 10:00 AM

SparkByte
Yak Posting Veteran

60 Posts

Posted - 2010-12-20 : 15:57:15

Thanks Lamprey.

... Back to the drawing board, so to speak.

Thank You,

John

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2010-12-20 : 16:33:43

Even as I am typing this, I don't like the query I am posting below - it is not scalable, and it looks too convoluted; I hope someone else will provide a better answer. And, even though the OP explicitly stated that time cannot be used as an ordering criterion, I am using time in this example:

Create Table #X
(
	SystemName Varchar(10),
	SysID Int,
	SysDateTime DateTime
)

Insert into #X Values('Apple', 3, '12/1/2010 11:04 AM');
Insert into #X Values('Apple', 4, '12/1/2010 11:58 AM');
Insert into #X Values('Apple', 1, '12/1/2010 11:00 AM');
Insert into #X Values('Apple', 1, '12/1/2010 11:01 AM');
Insert into #X Values('Apple', 1, '12/1/2010 09:05 AM');
Insert into #X Values('Apple', 2, '12/1/2010 09:10 AM');
Insert into #X Values('Apple', 3, '12/1/2010 09:40 AM');
Insert into #X Values('Apple', 4, '12/1/2010 10:00 AM');
Insert into #X Values('Apple', 2, '12/4/2010 03:25 PM');
Insert into #X Values('Apple', 1, '12/4/2010 12:47 PM');

with cte as
(
   select 
      row_number() over (partition by SystemName order by SysDateTime) as N, -- or whatever the ordering and partitioning criteria are
      *
   from #X
),
B as
(
select
   c.SystemName,
   c.N as i1,
   i2,i3,i4
from
   cte c
   cross apply (select case when x.SysId = c.SysId+1 then N end as i2 from cte x where x.n = c.n+1 and x.SystemName = c.SystemName) x
   cross apply (select case when y.SysId = c.SysId+2 then N end as i3 from cte y where y.n = c.n+2 and y.SystemName = c.SystemName) y
   cross apply (select case when z.SysId = c.SysId+3 then N end as i4 from cte z where z.n = c.n+3 and z.SystemName = c.SystemName) z
where
   i2 is not null and i3 is not null and i4 is not null and c.SysID = 1
)
select 
   t.SystemName,
   SysID,
   SysDateTime 
from 
   cte t inner join B 
      on (b.i1 = t.N or b.i2 = t.N or b.i3 = t.N or b.i4 = t.N) and t.SystemName = b.SystemName;

SparkByte
Yak Posting Veteran

60 Posts

Posted - 2010-12-20 : 16:57:29

This might just be it.

Create Table #X
(
	SystemName Varchar(10),
	SysID Int,
	SysDateTime DateTime
)

Insert into #X Values('Apple', 3, '12/1/2010 11:04 AM');
Insert into #X Values('Apple', 4, '12/1/2010 11:58 AM');
Insert into #X Values('Apple', 1, '12/1/2010 11:00 AM');
Insert into #X Values('Apple', 1, '12/1/2010 11:01 AM');
Insert into #X Values('Apple', 1, '12/1/2010 09:05 AM');
Insert into #X Values('Apple', 2, '12/1/2010 09:10 AM');
Insert into #X Values('Apple', 3, '12/1/2010 09:40 AM');
Insert into #X Values('Apple', 4, '12/1/2010 10:00 AM');
Insert into #X Values('Apple', 2, '12/4/2010 03:25 PM');
Insert into #X Values('Apple', 1, '12/4/2010 12:47 PM');


	Select 
		t.SystemName, 
		t.SysID, 
		t.SysDateTime
	From #X t
		Where t.SysDateTime In (Select Min(SysDateTime) As SysDateTime From #X Where SysID IN (1))
		OR t.SysDateTime In (Select Min(SysDateTime) As SysDateTime From #X Where SysID IN (2))
		OR t.SysDateTime In (Select Min(SysDateTime) As SysDateTime From #X Where SysID IN (3))
		OR t.SysDateTime In (Select Min(SysDateTime) As SysDateTime From #X Where SysID IN (4))

Drop Table #X

RESULT

SystemName SysID       SysDateTime
---------- ----------- -----------------------
Apple      1           2010-12-01 09:05:00.000
Apple      2           2010-12-01 09:10:00.000
Apple      3           2010-12-01 09:40:00.000
Apple      4           2010-12-01 10:00:00.000

Thank You,

John

SparkByte
Yak Posting Veteran

60 Posts

Posted - 2010-12-20 : 17:02:26

forgot the order by.

Order By t.SysID,t.SysDateTime

Thank You,

John

Bell
Starting Member

4 Posts

Posted - 2010-12-20 : 18:08:12

Thanks everyone for the response. I really appreciate it.

I cannot create a table like you have mentioned because the database has more than a million entries.

Also, Xtable has various IDs other than just 1,2, 3, and 4.

I wrote a simple query I wrote to extract 1, 2, 3 and 4 from the database:

SELECT Name, ID, time
FROM Xtable
WHERE (Name='Apple') AND ID in ('1', '2', '3', '4')
GROUP BY Name, ID, time

Like I mentioned before, the problem with this is it returns rows as long as it contains any of the above numbers

SparkByte
Yak Posting Veteran

60 Posts

Posted - 2010-12-20 : 19:12:01

You might want to create a lookup table or use a select statement to get all the possible ID's.

Thank You,

John

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources