Help with query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Fred - 13 Jun 2006 17:49 GMT

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.

Reply to this Message

Knut Stolze - 13 Jun 2006 18:13 GMT

> Thanks in advance for your help.
> Given the following table:
[quoted text clipped - 24 lines]
> 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...

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

Art S. Kagel - 13 Jun 2006 21:46 GMT

> Thanks in advance for your help.
> Given the following table:
[quoted text clipped - 18 lines]
> 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
[quoted text clipped - 3 lines]
>
> Thanks again for your help.

Reply to this Message

Knut Stolze - 14 Jun 2006 07:47 GMT

>> Thanks in advance for your help.
>> Given the following table:
[quoted text clipped - 24 lines]
> from t1
> group by 3;

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

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

William Bub - 14 Jun 2006 03:05 GMT

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"?

> Thanks in advance for your help.
> Given the following table:
[quoted text clipped - 26 lines]
>
> Thanks again for your help.

Reply to this Message

Fred - 14 Jun 2006 14:27 GMT

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.

> Am I missing something? Why wouldn't the "expected results" be:
> Beg End Value
[quoted text clipped - 43 lines]
> >
> > Thanks again for your help.

Reply to this Message

Tonkuma - 15 Jun 2006 16:34 GMT

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

Reply to this Message

Tonkuma - 18 Jun 2006 04:39 GMT

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.

Reply to this Message

Tonkuma - 18 Jun 2006 04:54 GMT

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.

Reply to this Message

4.spam@mail.ru - 14 Jun 2006 07:11 GMT

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,
[quoted text clipped - 23 lines]
>
> Thanks again for your help.

Reply to this Message

Andrey Odegov - 14 Jun 2006 09:21 GMT

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
avodeGOV@yandex.ru
(remove GOV to respond)

Reply to this Message

Andrey Odegov - 14 Jun 2006 09:44 GMT

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
avodeGOV@yandex.ru
(remove GOV to respond)

Reply to this Message

Fred - 14 Jun 2006 17:01 GMT

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

> sorry, i did a mistake in the query
>
[quoted text clipped - 15 lines]
> avodeGOV@yandex.ru
> (remove GOV to respond)

Reply to this Message

--CELKO-- - 16 Jun 2006 20:13 GMT

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;

Reply to this Message