SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases


Monday, June 19, 2006

Help with query

SQL Apprentice Question
Thanks in advance for your help.
Given the following table:

CREATE TABLE T1 (
seq INTEGER NOT NULL,
value CHAR(1) NOT NULL)


INSERT INTO TABLE T1 VALUES (1, 'A')
INSERT INTO TABLE T1 VALUES (2, 'A')
INSERT INTO TABLE T1 VALUES (3, 'A')
INSERT INTO TABLE T1 VALUES (5, 'B')
INSERT INTO TABLE T1 VALUES (6, 'B')
INSERT INTO TABLE T1 VALUES (7, 'A')


I would like to write a query that would return the beginning and
ending seq for each value. The desired results for the above data
would be as follows:


Beg End Value
1 3 A
5 6 B
7 7 A


I'm hesitant to post the queries I have been trying, because I don't
want to taint someone's thinking, but I have been approaching this with
the idea that a self join will be part of the solution. Simply
grouping by value using max and min functions will not return the
desired results.



Celko Answers
Another version with the outer limits of each sequential run. I assume
a table of sequential numbers called Sequence and the little-used ALL()
predicate.

SELECT val, MIN(lft), rgt
FROM (
SELECT F1.val, S1.seq, MAX(S2.seq)
FROM Foobar AS F1, Sequence AS S1, Sequence AS S2
WHERE S2.seq BETWEEN S1.seq AND (SELECT MAX(seq) FROM Foobar)
AND F1.val
= ALL(SELECT val
FROM Foobar AS F2
WHERE F2.seq BETWEEN S1.seq AND S2.seq
AND S1.seq <= S2.seq)
GROUP BY F1.val, S1.seq) AS X (val, lft, rgt)
GROUP BY X.val, X.rgt;

No comments: