How to join sequential values

Question

I do have a file that contains 2 kinds of records, parents and children:

id type filler
-- ---- ------
1  1500 h1
2  1501 d11
3  1501 d12
4  1501 d13
5  1500 h2
6  1501 d21
7  1501 d22
...

I would like to join records 1501 to respective 1500 record, so, result must be something similar to:

id1 type filler  id2 type filler2
--- ---- ------  --- ---- -------
1   1500 h1      2   1501 d11
1   1500 h1      3   1501 d12
1   1500 h1      4   1501 d13
5   1500 h2      6   1501 d21
5   1500 h2      7   1501 d22
...

Is there any way to join parent-child records like these?

Answer 1

To get the order from id and type, the easiest is probably to use common table expressions to tag the rows with their group and join rows of the same group to get the result, something close to;

WITH cte AS (  
  SELECT *, CASE WHEN type < LAG(type) OVER (ORDER BY id) THEN 1 END cnt 
  FROM mytable
), cte2 AS (                        
  SELECT id, type, filler, COUNT(cnt) OVER (ORDER BY id) tag FROM cte
) 
SELECT a.id, a.type, a.filler, b.id, b.type, b.filler 
FROM cte2 a 
JOIN cte2 b 
  ON a.tag = b.tag
WHERE a.type = 1500 AND b.type = 1501
ORDER BY a.id, b.id



 id | type | filler | id | type | filler
----+------+--------+----+------+--------
  1 | 1500 | h1     |  2 | 1501 | d11
  1 | 1500 | h1     |  3 | 1501 | d12
  1 | 1500 | h1     |  4 | 1501 | d13
  5 | 1500 | h2     |  6 | 1501 | d21
  5 | 1500 | h2     |  7 | 1501 | d22