Postgres how to perform following query-CodePudding

Please bare with me as I do not know how to word my question and therefore am having troubles finding a solution.

I have set up a database with Postgresql.

I have 3 columns in one of my tables: ID, Date/Time, and Print Statement.

I would like to:

order my table by Date/Time in DESC order
Iterate through the rows
Save rows that contain "data" until "START" string encountered in Print Statement column
if "START" string is equal to "START:123456" return previously encountered rows and row containing "START" string
repeat process for the next 100000 rows

example rows:

ID               DT                   PS
1                10/5/22 16:03:50     'data'
2                10/5/22 16:03:49     'Start:123456'
3                10/5/22 16:03:48     'data'
4                10/5/22 16:03:47     'START:333333'
5                10/5/22 16:03:46     'data'
6                10/5/22 16:03:45     'data'
7                10/5/22 16:03:44     'data'
8                10/5/22 16:03:43     'START:123456'
9                10/5/22 16:03:42     'data'
10               10/5/22 16:03:41     'data'

query should return:

1                10/5/22 16:03:50     'data'
2                10/5/22 16:03:49     'Start:123456'
5                10/5/22 16:03:46     'data'
6                10/5/22 16:03:45     'data'
7                10/5/22 16:03:44     'data'
8                10/5/22 16:03:43     'START:123456'

CodePudding user response：

This should get you what you need:

SELECT
    ID,
    DT,
    PS
FROM
    ( -- table with calculated NextStartPS column
    SELECT
        *,
        ( -- subquery that determines next START PS
        SELECT
            PS
        FROM
            TableName X -- alias to avoid namespace confusion
        WHERE
            ID >= TableName.ID
            AND PS LIKE 'START:%'
        ORDER BY
            ID
        LIMIT 1
        ) AS NextStartPS
    FROM
        TableName
    ) TableNameWithNextStartPS
WHERE
    NextStartPS = 'START:123456'
ORDER BY
    ID
;

First, add a NextStartPS column to your original table using a subquery. Then, query that contrived table, selecting rows only if NextStartPS = 'START:123456'.

Research subqueries a bit; they mimic procedural/iterative logic and can be very useful. Be careful though: subqueries are slow for this reason and should only be used when a problem calls for it! :)