CODEWITHSUNDEEP
sqlpostgresqlplpgsqlrelational-division
Chandrasen D Rajashekar
Chandrasen D Rajashekar

Reputation: 45

Return all the rows for id's that have all three seq number present

I have a table which looks something like this:

table1

+----+-----+------+
| id | seq | test |
+----+-----+------+
|  1 |   1 | HR   |
|  1 |   2 | RR   |
|  2 |   1 | HR   |
|  2 |   2 | RR   |
|  2 |   3 | OXY  |
|  3 |   1 | HR   |
|  3 |   2 | RR   |
|  4 |   1 | HR   |
|  4 |   2 | RR   | 
|  4 |   3 | OXY  | 
+----+-----+------+

I would like to get the result table like below. That is I need to have all the rows of a particular id only if all the three seq number is present for a particular id:

+----+-----+------+
| id | seq | test |
+----+-----+------+
|  2 |   1 | HR   |
|  2 |   2 | RR   |
|  2 |   3 | OXY  |
|  4 |   1 | HR   |
|  4 |   2 | RR   |
|  4 |   3 | OXY  | 
+----+-----+------+

I am looking forward to write a plpgsql function which gives me the solution. I am relatively new to plpgsql and programming in general. It would be great if someone help me out in getting the result.

So far this is what my function looks like and it is incomplete:

CREATE OR REPLACE FUNCTION test()
returns SETOF table1 AS $$
DECLARE
    cur CURSOR FOR
        SELECT *
        FROM table1
        ORDER by id;
    rec_cur RECORD;
    counter INTEGER DEFAULT 0;

BEGIN 
    OPEN cur;

    FETCH FIRST FROM cur INTO rec_cur;
    MOVE RELATIVE +1 FROM cur;

    LOOP
        FETCH cur INTO rec_cur;
        EXIT WHEN NOT FOUND;

        IF rec_cur.seq = 1 AND counter = 0 THEN
        RETURN NEXT rec_cursor;
        END IF;

    END LOOP;
    CLOSE cur;
    RETURN;

END ; $$ 
LANGUAGE PLPGSQL STABLE PARALLEL SAFE;

Upvotes: 2

Views: 85

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658947

Your question is incomplete.
If we can assume the existence of rows with seq = 1 and seq = 2 if there is a row with seq = 3 for the same id, then it becomes cheap and simple:

SELECT *
FROM  (SELECT id FROM table1 WHERE seq = 3) x
JOIN   table1 t USING (id)
-- ORDER BY id, seq;  -- unclear whether you need sorted output.

Also assuming (id, seq) to be defined UNIQUE and both column NOT NULL.

If you need to optimize read performance, add a partial index:

CREATE INDEX foo ON table1 (id) WHERE seq = 3;

Since Postgres 9.6 this can be used in an index-only scan.

And you need an index on (id) of course. The index on (id, seq) which exists if you have said UNIQUE constraint does the job as well. Related:

Either way, it's a case of . here is an arsenal of techniques to identify qualifying id's if we can't assume sequential values in seq:

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

A cursor is definitely not the right approach. You can the ids easily using aggregation and having:

select id
from t
where seq in (1, 2, 3)
group by id
having count(seq) = 3;

Then to get the original rows, there are multiple ways:

select t.*
from t join
     (select id
      from t
      where seq in (1, 2, 3)
      group by id
      having count(seq) = 3
     ) tt
     on t.id = tt.id;

EDIT:

If the sequence numbers always start at 1 and have no gaps, then window functions are the way to go:

select t.*
from (select t.*, max(t.seq) over (partition by t.id) as maxseq
      from t
     ) t
where maxseq = 3;

Upvotes: 2

Related Questions