search inside partitions
Olivier Leprêtre <[email protected]>
| Newsgroups | gmane.comp.db.postgresql.sql |
|---|---|
| Message-ID | <[email protected]> |
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
image001.png
(image/png, 56.8 KB) - not displayed