SQL by selecting adjacent rows for a neighboring set

Question

I am having trouble doing the following in SQL with Postgres. My program has an ordered set of numbers. My database has a table that stores all the numbers in the rows with additional data. These lines are also ordered.

For example, my set that I need to find is:

1,5,6,1,3 

There are rows in the database

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

In the above example, it's easy to see that my set is found from line 3 to line 7. Still doing this in SQL is a mystery to me. I read articles about pivot tables, but I hope there will be an easier way.

Answer 1

Both datasets must have fields that define the order.

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

 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 request ...

 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 

Answer 2

Recursive version

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

• This does not require an additional table. Just insert your consecutive numbers as an array.
• Recursive CTE in itself is simpler.
• The final request is smarter.
• It really works in PostgreSQL. The recursive version of @Dems is not syntactically correct in the 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 = xa[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 = xa[1 + step] -- next value ) SELECT y.start_id FROM x JOIN y ON y.step = array_length(xa, 1) -- only where last steps was matched 

Result:

 3 

---

Static version

Works for a predetermined number of array elements, but 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 = xa[1] AND t2.val = xa[2] AND t3.val = xa[3] AND t4.val = xa[4] AND t5.val = xa[5]; 

Answer 3

What about...

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

It kills that my SQL should look for similar functions for Postgresql ...

Postgresql Version of Group_concat

All this means is that the group has several lines in one long line, and then do "Find" to return the first position of your line in the generated long line. The returned number will match row_number. If 0 is returned, your string is not in the generated one. (you may need to be careful with the comma.

Answer 4

The 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)