OOPS - correction to my previous reply (not shown up yet)...
The query would fail if there were multiple IPClasses (for example 10.10.10
and 10.10.20) in the table.
Corrected query:
Select V1.IPNumber,
(V2.IPNumber - V1.IPNumber + 1) as SetSize,
V1.IPClass
FROM (
Select T1.IPNumber, T1.IPClass,
(Select Count(*) From IPTable T3
Where ((T3.IPClass = T1.IPClass
And T3.IPNumber <= T1.IPNumber)
Or (T3.IPClass < T1.IPClass))
And Not Exists (Select 1
from IPTable as T4
Where T4.IPClass = T3.IPClass
And T4.IPNumber = T3.IPNumber - 1)) as Seq
>From IPTable as T1
Where Not Exists (Select 1
from IPTable as T2
Where T2.IPClass = T1.IPClass
And T2.IPNumber = T1.IPNumber - 1)
) as V1
INNER JOIN (
Select T1.IPNumber, T1.IPClass,
(Select Count(*) From IPTable T3
Where ((T3.IPClass = T1.IPClass
And T3.IPNumber <= T1.IPNumber)
Or (T3.IPClass < T1.IPClass))
And Not Exists (Select 1
from IPTable as T4
Where T4.IPClass = T3.IPClass
And T4.IPNumber = T3.IPNumber + 1)) as Seq
>From IPTable as T1
Where Not Exists (Select 1
from IPTable as T2
Where T2.IPClass = T1.IPClass
And T2.IPNumber = T1.IPNumber + 1)
) as V2
ON V1.Seq = V2.Seq
HTH,
Tore.
-----Original Message-----
From: Zaid Abdur-Rahman [mailto:[EMAIL PROTECTED]]
Sent: Monday, August 19, 2002 3:14 PM
To: ActiveServerPages
Subject: SQL Select
Hello All
If I have a table with the following format
Table
IPNumber
IPClass
DATA
0,10.10.10
1,10.10.10
2,10.10.10
3,10.10.10
7,10.10.10
8,10.10.10
9,10.10.10
20,10.10.10
21,10.10.10
I would like to have a select statement that would produce the following,
which is the first number in a set and how many records are in that set.
0,4,10.10.10
7,3,10.10.10
20,2,10.10.10
Is this possible?
Thanks for your knowledge
Zaid Abdur-Rahman
[EMAIL PROTECTED]
---
You are currently subscribed to activeserverpages as: [EMAIL PROTECTED]
To unsubscribe send a blank email to
%%email.unsub%%
---
You are currently subscribed to activeserverpages as: [email protected]
To unsubscribe send a blank email to [EMAIL PROTECTED]
