how to obtain ranges of serial numbers in sql?

Question

I have a table with a field called sector, each sector is usually something like 1,2,3,4,5,6,7,etc.

I want to show available sectors in an application, I thought that showing all 1,2,3,4,5,6,7 is dumb so I should show "1 to 7" instead.

The problem is that sometimes the sectors skip one number like this 1,2,3, 5,6,7. So I want to show something like 1 to 3, 5 to 7.

how could I query this in sql to show in my app?

Answer 1

ok, I have been looking deeper and found this

It works :), hope it helps someone as it helped me.

Answer 2

This is called "Gaps" in sql. Here is a detailed article "Article"

Answer 3

Some DBMS might have some OLAP functionality that makes it easy to write such queries, but IBM Informix Dynamic Server (IDS) does not yet have such functions.

Let's assume, for sake of concreteness, that your table is called 'ProductSectors' and has a structure like:

CREATE TABLE ProductSectors
(
    ProductID    INTEGER NOT NULL,
    Sector       INTEGER NOT NULL CHECK (Sector > 0),
    Name         VARCHAR(20) NOT NULL,
    PRIMARY KEY (ProductID, Sector)
);

What you are seeking within a particular ProductID is a list of the minimum and maximum contiguous values of Sector. A range is contiguous when there is no value one smaller than the minimum and no value one bigger than the maximum and there is no gap within the range. This is a complex query:

SELECT P1.ProductID, P1.Sector AS Min_Sector, P2.Sector AS Max_Sector
  FROM ProductSectors P1 JOIN ProductSectors P2
    ON P1.ProductID = P2.ProductID
   AND P1.Sector   <= P2.Sector
 WHERE NOT EXISTS (SELECT *     -- no entry one smaller
                     FROM ProductSectors  P6
                    WHERE P1.ProductID  = P6.ProductID
                      AND P1.Sector - 1 = P6.Sector
                  )
   AND NOT EXISTS (SELECT *     -- no entry one larger
                     FROM ProductSectors  P5
                    WHERE P2.ProductID  = P5.ProductID
                      AND P2.Sector + 1 = P5.Sector
                  )
   AND NOT EXISTS (SELECT *     -- no gaps between P1.Sector and P2.Sector
                     FROM ProductSectors P3
                    WHERE P1.ProductID = P3.ProductID
                      AND P1.Sector   <= P3.Sector
                      AND P2.Sector   >  P3.Sector
                      AND NOT EXISTS (SELECT *
                                        FROM ProductSectors P4
                                       WHERE P4.ProductID = P3.ProductID
                                         AND P4.Sector    = P3.Sector + 1
                                     )
                  )
 ORDER BY P1.ProductID, Min_Sector;

And here is a trace of the overall query working with sample data:

CREATE TEMP TABLE productsectors
(
    ProductID   INTEGER NOT NULL,
    Sector      INTEGER NOT NULL CHECK(Sector > 0),
    Name        VARCHAR(20),
    PRIMARY KEY (ProductID, Sector)
);

And some sample data, with various gaps:

INSERT INTO ProductSectors VALUES(101, 1, "101:1");
INSERT INTO ProductSectors VALUES(101, 2, "101:2");
INSERT INTO ProductSectors VALUES(101, 3, "101:3");
INSERT INTO ProductSectors VALUES(101, 4, "101:4");
INSERT INTO ProductSectors VALUES(101, 5, "101:5");
INSERT INTO ProductSectors VALUES(101, 6, "101:6");
INSERT INTO ProductSectors VALUES(101, 7, "101:7");
INSERT INTO ProductSectors VALUES(102, 1, "102:1");
INSERT INTO ProductSectors VALUES(102, 2, "102:2");
INSERT INTO ProductSectors VALUES(102, 4, "102:4");
INSERT INTO ProductSectors VALUES(102, 5, "102:5");
INSERT INTO ProductSectors VALUES(102, 6, "102:6");
INSERT INTO ProductSectors VALUES(102, 7, "102:7");
INSERT INTO ProductSectors VALUES(103, 1, "103:1");
INSERT INTO ProductSectors VALUES(103, 2, "103:2");
INSERT INTO ProductSectors VALUES(103, 4, "103:4");
INSERT INTO ProductSectors VALUES(103, 6, "103:6");
INSERT INTO ProductSectors VALUES(103, 7, "103:7");
INSERT INTO ProductSectors VALUES(104, 1, "104:1");
INSERT INTO ProductSectors VALUES(104, 2, "104:2");
INSERT INTO ProductSectors VALUES(104, 3, "104:3");
INSERT INTO ProductSectors VALUES(104, 6, "104:6");
INSERT INTO ProductSectors VALUES(104, 7, "104:7");
INSERT INTO ProductSectors VALUES(105, 1, "105:1");
INSERT INTO ProductSectors VALUES(105, 4, "105:4");
INSERT INTO ProductSectors VALUES(105, 5, "105:5");
INSERT INTO ProductSectors VALUES(105, 7, "105:7");
INSERT INTO ProductSectors VALUES(106, 1, "106:1");
INSERT INTO ProductSectors VALUES(106, 2, "106:1");
INSERT INTO ProductSectors VALUES(106, 3, "106:1");
INSERT INTO ProductSectors VALUES(106, 7, "106:7");
INSERT INTO ProductSectors VALUES(107, 7, "107:7");
INSERT INTO ProductSectors VALUES(108, 8, "108:8");
INSERT INTO ProductSectors VALUES(108, 9, "108:9");

Required output - also the actual output:

101|1|7 
102|1|2 
102|4|7 
103|1|2 
103|4|4 
103|6|7 
104|1|3 
104|6|7 
105|1|1 
105|4|5 
105|7|7 
106|1|3 
106|7|7 
107|7|7 
108|8|9 

With the expected results on MacOS X 10.6.2, IDS 11.50.FC4W1, SQLCMD 86.04.