Assuming you are using a "break" in the numbering scheme to indicate the end
of a set, it is possible, but not quite trivial. Based on your needs, it
may be easier to produce the output by parsing the simple record set in your
code.
A SQL implementation approach would be based on identifying the first
IPNumber and IPClass in each set, using the following query:
Select T1.IPNumber, T1.IPClass
>From IPTable as T1
Where Not Exists (Select 1
from IPTable as T2
Where T2.IPClass = T1.IPClass
And T2.IPNumber = T1.IPNumber - 1)
Based on this query, you have the "starting" IPNumber and IPClass. By
joining two variations of the above query (the other one using T2.IPNumber =
T1.IPNumber + 1) on the "Interval Number" (a trick that allows us to assign
a "sequential number" to a result row by mimicking the query and counting
the result rows that come before or matches by primary key or unique
constraint), we get the rather convoluted solution (works for SQL Server -
probably not for MS Access - didn't try it):
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
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)
Order By T1.IPClass, T1.IPNumber
) as V1
INNER JOIN (
Select T1.IPNumber, T1.IPClass,
(Select Count(*) From IPTable T3
Where T3.IPClass <= T1.IPClass
And T3.IPNumber <= T1.IPNumber
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)
Order By T1.IPClass, T1.IPNumber
) as V2
ON V1.Seq = V2.Seq
Since this app appears to address Intranet address ranges, I assume the
database will be rather small, and performance acceptable or non-critical...
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]
