Re: search inside partitions

Hi Olivier,

since this is some sort of "pointer chasing" through the table, I guess a
recursive SQL function can do the trick.

See the SQL code below which it appears to do the job (modulo any false
interpretation of your problem statement).

Cheers,

—Torsten

DROP TABLE IF EXISTS t;
CREATE TABLE t (
a INT NOT NULL,
b INT NOT NULL);

INSERT INTO t(a,b) VALUES
( 298, 151208),
(151208, 151209),
(151210, 2000),
( 2000, 151211),
( 2004, 151213),
( 2004, 2013),
( 27601, 151214),
(151214, 151215),
(151212, 151213),
( 2016, 2018),
(151215, 2016),
( 2018, 2019),
( 2019, 2020),
( 2020, 151217),
(151217, 151218),
( 2026, 2028),
( 33753, 2026),
( 2013, 27601),
(151218, 151219),
(151219, 33753),
(151209, 151210),
(151211, 151212);

WITH RECURSIVE chase(a, b, pos) AS (
SELECT t.a, t.b, 1 AS pos
FROM T AS t
WHERE t.a = 298 -- identifies the "first row" in the input

UNION ALL

SELECT CASE WHEN c.b = t.a THEN t.a ELSE t.b END AS a,
CASE WHEN c.b = t.a THEN t.b ELSE t.a END AS b,
c.pos + 1 AS pos
FROM chase AS c, T AS t
WHERE (c.b = t.a AND c.a <> t.b) OR (c.b = t.b AND c.a <> t.a)
)
TABLE chase;

On Fri, Feb 2, 2018 at 10:00 AM Olivier Leprêtre <o(dot)lepretre(at)gmail(dot)com>
wrote:

> Hi to all,
>
>
>
> I have a table, from which I can extract partitions which need to be
> ordered, one after the other. A partition is composed of 2 columns A and B
> and ordering is done by finding the next value of B in another row of the
> partition.
>
>
>
> Here are the two columns of one of the partitions :
>
>
>
> 298
>
> 151208
>
> 151208
>
> 151209
>
> 151210
>
> 2000
>
> 2000
>
> 151211
>
> 2004
>
> 151213
>
> 2004
>
> 2013
>
> 27601
>
> 151214
>
> 151214
>
> 151215
>
> 151212
>
> 151213
>
> 2016
>
> 2018
>
> 151215
>
> 2016
>
> 2018
>
> 2019
>
> 2019
>
> 2020
>
> 2020
>
> 151217
>
> 151217
>
> 151218
>
> 2026
>
> 2028
>
> 33753
>
> 2026
>
> 2013
>
> 27601
>
> 151218
>
> 151219
>
> 151219
>
> 33753
>
> 151209
>
> 151210
>
> 151211
>
> 151212
>
>
>
> Starting from the first row the goal is to find the same value in previous
> of following rows, column A or B.
>
> Example : starting from row 1, 15208 returns row 2 15208-15209, 15209
> returns row 15209-15210, 15210 returns 15210-2000 and so on. When the next
> row is find, a third column C is incremented to be used for sorting
>
> [image: image001.png]
>
>
>
> results should then be
>
> 298
>
> 151208
>
> 1
>
> 151208
>
> 151209
>
> 2
>
> 151209
>
> 151210
>
> 3
>
> 151210
>
> 2000
>
> 4
>
> 2000
>
> 151211
>
> 5
>
> 151211
>
> 151212
>
> 6
>
> 151212
>
> 151213
>
> 7
>
> 151213
>
> 2004
>
> 8
>
> 2004
>
> 2013
>
> 9
>
> 2013
>
> 27601
>
> 10
>
> 27601
>
> 151214
>
> 11
>
> 151214
>
> 151215
>
> 12
>
> 151215
>
> 2016
>
> 13
>
> 2016
>
> 2018
>
> 14
>
> 2018
>
> 2019
>
> 15
>
> 2019
>
> 2020
>
> 16
>
> 2020
>
> 151217
>
> 17
>
> 151217
>
> 151218
>
> 18
>
> 151218
>
> 151219
>
> 19
>
> 151219
>
> 33753
>
> 20
>
> 33753
>
> 2026
>
> 21
>
> 2026
>
> 2028
>
> 22
>
>
>
> By the way, switching has to be done to have always previous B -> next A
> but this is less important as it can be done using tricks like putting a
> minus sign when required and switching afterwards.
>
>
>
> I'm wondering if there is a rather simple way to do that using partition
> functions or if I should better try do that using C#
>
>
>
> Thanks for any idea,
>
>
>
> Olivier
>
>
>
>
> <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient> Garanti
> sans virus. www.avast.com
> <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient>
> <#m_-3690016585929685927_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
>
--
| Prof. Dr. Torsten Grust
| Database Systems — Universität Tübingen (Germany)
| ✉︎ torsten(dot)grust(at)uni-tuebingen(dot)de
| db.inf.uni-tuebingen.de