grouping records

  • Thread starter Thread starter jimw
  • Start date Start date
J

jimw

How can I take a non-contiguous range of numbers and
create a report which shows the contiguous ranges
contained within?
 
Assume your table is called tblRanges with field ID.

Create this query and call it qRangeMin:
SELECT tblRanges.ID
FROM tblRanges, tblRanges AS R
WHERE (((tblRanges.ID)=[R].[id]+1));

Create this query and call it qRangeStart:
SELECT tblRanges.ID
FROM tblRanges LEFT JOIN qRangeMin ON tblRanges.ID = qRangeMin.ID
WHERE (((qRangeMin.ID) Is Null));

Create this query and call it qRangeMax:
SELECT tblRanges.ID
FROM tblRanges, tblRanges AS R
WHERE (((tblRanges.ID)=[R].[id]-1));

Create this query and call it qRangeEnd:
SELECT tblRanges.ID
FROM tblRanges LEFT JOIN qRangeMax ON tblRanges.ID = qRangeMax.ID
WHERE (((qRangeMax.ID) Is Null));

Then run this query:
SELECT qRangeStart.tblRanges.ID AS Start, (select min(E.id) from qRangeEnd
as E where E.ID>=qRangeStart.id) AS End
FROM qRangeStart;
 
This method was first proposed by
Steve Kass of Drew University.

CREATE TABLE test1 (x int NOT NULL );

INSERT INTO test1 (x) VALUES(1);
INSERT INTO test1 (x) VALUES(2);
INSERT INTO test1 (x) VALUES(3);
INSERT INTO test1 (x) VALUES(6);
INSERT INTO test1 (x) VALUES(7);
INSERT INTO test1 (x) VALUES(8);
INSERT INTO test1 (x) VALUES(13);
INSERT INTO test1 (x) VALUES(16);
INSERT INTO test1 (x) VALUES(17);

Append an autonumber to each row.
ALTER table test1 add rowid autoincrement;

Perform the major query.
select min(x) as start,max(x) as end
from test1
group by x-rowid
order by 1;

start end
1 3
6 8
13 13
16 17

For Sql Server 2000 crosstabs and more check
out RAC @
www.rac4sql.net
 
Back
Top