agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Olivier Leprêtre <o.lepretre@gmail.com>
To: pgsql-sql@lists.postgresql.org
Subject: search inside partitions
Date: Fri, 2 Feb 2018 10:00:28 +0100
Message-ID: <5a7428b2.d5171c0a.48427.4065@mx.google.com> (raw)
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
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
---
L'absence de virus dans ce courrier électronique a été vérifiée par le logiciel antivirus Avast.
https://www.avast.com/antivirus
Attachments:
[image/png] image001.png (56.8K, ../5a7428b2.d5171c0a.48427.4065@mx.google.com/3-image001.png)
download | view image
view thread (4+ messages) latest in thread
Message-ID: <5a7428b2.d5171c0a.48427.4065@mx.google.com>
Permalink: ../5a7428b2.d5171c0a.48427.4065@mx.google.com/
Also on: postgresql.org/message-id/5a7428b2.d5171c0a.48427.4065@mx.google.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: o.lepretre@gmail.com, pgsql-sql@lists.postgresql.org
Subject: Re: search inside partitions
In-Reply-To: <5a7428b2.d5171c0a.48427.4065@mx.google.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox