CODEWITHSUNDEEP
sqlpostgresql
Casper Broeren
Casper Broeren

Reputation: 780

SQL selecting adjacent rows for a adjacent set

I'm having trouble doing the following in SQL with Postgres. My program has an ordered set of numbers. In my database I have the table which stores all numbers in rows with extra data. These rows are also placed in order.

For example my set I need to find is;

1,5,6,1,3

The database has rows

row1 4
row2 5
row3 1
row4 5
row5 6
row6 1
row7 3
row8 2
row9 7

In the example above it's easy to see that my set is found from row 3 to to row7. Still doing such in SQL is a mystery to me. I'm reading some articles regarding pivot tables, still I'm hoping there's an easier way.

Upvotes: 4

Views: 1226

Answers (4)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657002

Recursive version

@Dems beat me to it: a recursive CTE is the way to go here. It works for any sequence of numbers. I post my version because:

  • It does not require an additional table. Just insert your sequential numbers as array.
  • The recursive CTE itself is simpler.
  • The final query is smarter.
  • It actually works in PostgreSQL. @Dems recursive version is not syntactically correct in it's current state.

Test setup:

CREATE TEMP TABLE t (id int, val int);
INSERT INTO t VALUES
 (1,4),(2,5),(3,1)
,(4,5),(5,6),(6,1)
,(7,3),(8,2),(9,7);

Call:

WITH RECURSIVE x AS (
    SELECT '{1,5,6,1,3}'::int[] AS a
    ), y AS (
    SELECT t.id   AS start_id
          ,1::int AS step
    FROM   x
    JOIN   t ON t.val = x.a[1]

    UNION  ALL
    SELECT y.start_id
          ,y.step + 1                      -- AS step   -- next step
    FROM   y
    JOIN   t ON t.id = y.start_id + step   -- next id
    JOIN   x ON t.val = x.a[1 + step]      -- next value
    )
SELECT y.start_id
FROM   x
JOIN   y ON y.step = array_length(x.a, 1)  -- only where last steps was matched

Result:

3

Static version

Works for a predefined number of array items, but is faster for small arrays. 5 items in this case. Same test setup as above.

WITH x AS (
    SELECT '{1,5,6,1,3}'::int[] AS a
    )
SELECT t1.id
FROM   x, t t1
JOIN   t t2 ON t2.id = t1.id + 1
JOIN   t t3 ON t3.id = t1.id + 2
JOIN   t t4 ON t4.id = t1.id + 3
JOIN   t t5 ON t5.id = t1.id + 4
WHERE  t1.val = x.a[1]
AND    t2.val = x.a[2]
AND    t3.val = x.a[3]
AND    t4.val = x.a[4]
AND    t5.val = x.a[5];

Upvotes: 2

MatBailie
MatBailie

Reputation: 86735

Recursive answer...

WITH
  CTE AS
(
  SELECT
    id        AS first_id,
    id        AS current_id,
    1         AS sequence_id
  FROM
    main_table
  WHERE
    value = (SELECT value FROM search_table WHERE id = 1)

  UNION ALL

  SELECT
    CTE.first_id,
    main_table.id,
    CTE.sequence_id + 1
  FROM
    CTE
  INNER JOIN
    main_table
      ON main_table.id = CTE.current_id + 1
  INNER JOIN
    search_table
      ON  search_table.value = main_table.value
      AND search_table.id    = CTE.sequence_id + 1
)
SELECT
  *
FROM
  main_table
INNER JOIN
  CTE
    ON  main_table.id >= CTE.first_id
    AND main_table.id <= CTE.current_id
WHERE
  CTE.sequence_id = (SELECT COUNT(*) FROM search_table)

Upvotes: 0

xQbert
xQbert

Reputation: 35333

how about...

Select instr(',' & Group_Concat(mNumber SEPARATOR ',') &',',@yourstring)
FROM Table

Whoops that's my SQL have to look up similar functions for Postgresql...

Postgresql Version of Group_concat

All this does is group multiple rows into one long string and then do a "Find" to return the first position of your string in the generated long string. The returned number will match the row_number. If 0 is returned your string isn't in the generated one. (may have to be cautious with the ', ' comma space.

Upvotes: 0

MatBailie
MatBailie

Reputation: 86735

Both data-sets need to have fields that identify the order.

And provided that the ordering column is a sequential consecutive set of numbers, then this is possible, although I doubt it's very quick.

Table 1                 Table 2

id | value              id | value
 1    4                  1    1
 2    5                  2    5
 3    1                  3    6
 4    5                  4    1
 5    6                  5    3
 6    1                  
 7    3                  
 8    2                  
 9    7

Then this query...

SELECT
  *
FROM
  table_1
INNER JOIN
  (
    SELECT
      MIN(table_1.id)    AS first_id,
      MAX(table_1.id)    AS last_id
    FROM
      table_1
    INNER JOIN
      table_2
        ON table_1.value = table_2.value
    GROUP BY
      table_1.id - table_2.id
    HAVING
      COUNT(*) = (SELECT COUNT(*) FROM table_2)
  )
  AS matched_sets
    ON  matched_sets.first <= table_1.id
    AND matched_sets.last  >= table_1.id

Upvotes: 4

Related Questions