Help with query

[Fred]

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.

Thanks again for your help.

[Knut Stolze]

Fred wrote:

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.

This will return the lower boundary of a sequence:

SELECT seq, value
FROM t1 AS o
WHERE NOT EXISTS ( SELECT 1
FROM t1 AS i1
WHERE i1.seq = t1.seq - 1 AND
i1.value = t1.value )

Now you just extend this to add the upper boundary:

SELECT seq AS begin,
( SELECT MIN(seq)
FROM t1 AS i2
WHERE i2.value = t1.value AND
i2.seq >= t1.value AND
-- no successor
NOT EXISTS ( SELECT 1
FROM t1 AS i3
WHERE i3.seq = i2.seq + 1 AND
i3.value = t2.value ) ) AS end,
value
FROM t1 AS o
WHERE NOT EXISTS ( SELECT 1
FROM t1 AS i1
WHERE i1.seq = t1.seq - 1 AND
i1.value = t1.value )
This is untested and you can probably simplify this...

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Art S. Kagel]

Fred wrote:

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
How about:

select min(seq) as beg, max(seq) as end, value
from t1
group by 3;

Art S. Kagel

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.

Thanks again for your help.

[William Bub]

Am I missing something? Why wouldn't the "expected results" be:
Beg End Value
1 7 A
5 6 B
?

Are you looking for multiple contiguous ranges per "CHAR"?
If INSERT INTO TABLE T1 VALUES (2, 'A') were not present, would you
expect:
1 1 A
3 3 A
5 6 B
7 7 A
?

Or should the last line have actually been "C"?

"Fred" <fr**********@yahoo.com> wrote in message
news:11**********************@h76g2000cwa.googlegr oups.com...

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.

Thanks again for your help.

[4.spam]

Hello.
---
with t1(i, c) as
( values
(1, 'A')
,(2, 'A')
,(3, 'A')
,(5, 'B')
,(6, 'B')
,(7, 'A')
),
R (B, E, C) AS
(
SELECT I AS B, I AS E, C
FROM T1 A
WHERE NOT EXISTS
(
SELECT 1
FROM T1 B
WHERE A.C=B.C AND B.I=A.I-1
)
UNION ALL
SELECT R.B, T1.I, R.C
FROM T1, R
WHERE T1.C=R.C AND T1.I=R.E+1
)
SELECT B, MAX(E) E, C
FROM R
GROUP BY C, B;
---

Sincerely,
Mark B.

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.

Thanks again for your help.

[Knut Stolze]

Art S. Kagel wrote:

Fred wrote:

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

How about:

select min(seq) as beg, max(seq) as end, value
from t1
group by 3;

This won't work because you don't detect the gap in the sequence for the
group A.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Andrey Odegov]

Hi, Fred.

How about a solution like this:

WITH T(seq, val, grp) AS(
SELECT L.seq, L.value, L.seq - COUNT(*)
FROM t1 AS L
INNER JOIN L AS R
ON R.value = L.value
AND R.seq <= L.seq
GROUP BY L.value, L.seq)
SELECT MIN(seq) AS beg, MAX(seq) AS end, val
FROM T
GROUP BY val, grp

---
Andrey Odegov
av******@yandex.ru
(remove GOV to respond)

[Andrey Odegov]

sorry, i did a mistake in the query

it must be the following instead:

WITH T(seq, val, grp) AS(
SELECT L.seq, L.value, L.seq - COUNT(*)
FROM t1 AS L
INNER JOIN t1 AS R
ON R.value = L.value
AND R.seq <= L.seq
GROUP BY L.value, L.seq)
SELECT MIN(seq) AS beg, MAX(seq) AS end, val
FROM T
GROUP BY val, grp

---
Andrey Odegov
av******@yandex.ru
(remove GOV to respond)

[Fred]

I am not trying to find the max and min seq for a given value. I'm
trying to show the upper and lower limits of a range for a given value.
Your latter example is correct, if (2,'A') were omitted, the results
would be as you have it.
William Bub wrote:

Am I missing something? Why wouldn't the "expected results" be:
Beg End Value
1 7 A
5 6 B
?

Are you looking for multiple contiguous ranges per "CHAR"?
If INSERT INTO TABLE T1 VALUES (2, 'A') were not present, would you
expect:
1 1 A
3 3 A
5 6 B
7 7 A
?

Or should the last line have actually been "C"?

"Fred" <fr**********@yahoo.com> wrote in message
news:11**********************@h76g2000cwa.googlegr oups.com...

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.

Thanks again for your help.

[Fred]

Thanks to all. Andrey's solution is very concise, quick and accurate.

Andrey Odegov wrote:

sorry, i did a mistake in the query

it must be the following instead:

WITH T(seq, val, grp) AS(
SELECT L.seq, L.value, L.seq - COUNT(*)
FROM t1 AS L
INNER JOIN t1 AS R
ON R.value = L.value
AND R.seq <= L.seq
GROUP BY L.value, L.seq)
SELECT MIN(seq) AS beg, MAX(seq) AS end, val
FROM T
GROUP BY val, grp

---
Andrey Odegov
av******@yandex.ru
(remove GOV to respond)

[Tonkuma]

Fred
What is your answer for William's quesstion?

If INSERT INTO TABLE T1 VALUES (2, 'A') were not present, would you
expect:
1 1 A
3 3 A
5 6 B
7 7 A

Or do you want this?
1 3 A
5 6 B
7 7 A

[--CELKO--]

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;

[Tonkuma]

Fred
If INSERT INTO TABLE T1 VALUES (2, 'A') were not present, which
result would you expect?
1)
1 1 A
3 3 A
5 6 B
7 7 A

Or

2)
1 3 A
5 6 B
7 7 A

By, using OLAP function, you can easily chane the result. DIfference is
only RANGE or ROWS in OLAP functons.

If you expect 1)
SELECT MIN(seq) AS Beg
, MAX(seq) AS End
, MAX(value) AS Value
FROM (SELECT seq, value, fol_value
, ROWNUMBER() OVER(ORDER BY seq) rn
FROM (SELECT seq
, value
, MAX(value) OVER(ORDER BY seq
RANGE BETWEEN 1 PRECEDING
AND 1 PRECEDING)
, MAX(value) OVER(ORDER BY seq
RANGE BETWEEN 1 FOLLOWING
AND 1 FOLLOWING)
FROM Fred.t1
) Q (seq, value, pre_value, fol_value)
WHERE value <> COALESCE(pre_value, '*')
OR value <> COALESCE(fol_value, '*')
) R
GROUP BY
rn + CASE WHEN value = fol_value THEN 1 ELSE 0 END
ORDER BY
Beg;
--------------------------------------------------------------------

BEG END VALUE
----------- ----------- -----
1 1 A
3 3 A
5 6 B
7 7 A

4 record(s) selected.

If you expect 2)
SELECT MIN(seq) AS Beg
, MAX(seq) AS End
, MAX(value) AS Value
FROM (SELECT seq, value, fol_value
, ROWNUMBER() OVER(ORDER BY seq) rn
FROM (SELECT seq
, value
, MAX(value) OVER(ORDER BY seq
ROWS BETWEEN 1 PRECEDING
AND 1 PRECEDING)
, MAX(value) OVER(ORDER BY seq
ROWS BETWEEN 1 FOLLOWING
AND 1 FOLLOWING)
FROM Fred.t1
) Q (seq, value, pre_value, fol_value)
WHERE value <> COALESCE(pre_value, '*')
OR value <> COALESCE(fol_value, '*')
) R
GROUP BY
rn + CASE WHEN value = fol_value THEN 1 ELSE 0 END
ORDER BY
Beg;
-------------------------------------------------------------------

BEG END VALUE
----------- ----------- -----
1 3 A
5 6 B
7 7 A

3 record(s) selected.

[Tonkuma]

Fred,
I'm very sorry.
I realized just now that you already answered Wiiliam's question.

if (2,'A') were omitted, the results would be as you have it.

I beg your perdon for my carelessness and to bother you.