RE: search inside partitions

Thanks very much Torsten, it works like a charm. I was not used to recursive functions, that's a good beginning...

 

Nice week-end to all 

 

De : Torsten Grust [mailto:[email protected]] 
Envoyé : vendredi 2 février 2018 12:20
À : [email protected]
Objet : 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 <[email protected]> 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

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.  <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient> www.avast.com 

-- 

| Prof. Dr. Torsten Grust

| Database Systems — Universität Tübingen (Germany)

| ✉︎ [email protected]

| db.inf.uni-tuebingen.de

-- 

| Prof. Dr. Torsten Grust

| Database Systems — Universität Tübingen (Germany)

| ✉︎ [email protected]

| db.inf.uni-tuebingen.de



---
L'absence de virus dans ce courrier électronique a été vérifiée par le logiciel antivirus Avast.
https://www.avast.com/antivirus