How to select all values that have the same chronological order?

Question

I'm saving all the moves a player does in a Table with the associated Game-Id.

The order in which the moves are executed is also saved.

Table:

id | move     | order
---+----------+------
 1 | Attack   | 1
 1 | Defense  | 2
 1 | Defense  | 3
 1 | Fly      | 4
 2 | Attack   | 1

For example: In game ID 1 he first Attack,Defense,Defense then Fly.

I now want to select all game-id's that have the same chronological order I specified.

PSEUDO CODE

SELECT DISTINCT id FROM Table
WHERE Player 
first Attack
second Defense
third Defense 

The Pseudo Code would for example return ID 1 even thought the fourth move Fly wasn't specified.

Answer 1

Assuming that you mean that you want the same initial set of moves, then I think this does what you want:

SELECT id
FROM (SELECT id, ARRAY_AGG(move ORDER BY "order") as moves
      FROM t
      WHERE order <= 3
     ) im
WHERE moves = ARRAY['Attack', 'Defense', 'Defense'];

This is only checking the first three moves. You might also phrase this as:

SELECT id
FROM (SELECT id, ARRAY_AGG(move ORDER BY "order") as moves
      FROM t
      WHERE order <= CARDINALITY(ARRAY['Attack', 'Defense', 'Defense'])
     ) im
WHERE moves = ARRAY['Attack', 'Defense', 'Defense'];

Answer 2

You could use row_number() and aggregation:

select id
from (select t.*, row_number() over(partition by id order by "order") rn from mytable t) t
group by id
having
    max(case when rn = 1 and move = 'Attack' then 1 end) = 1
    and max(case when rn = 2 and move = 'Defense' then 1 end) = 1
    and max(case when rn = 3 and move = 'Defense' then 1 end) = 1

Side note: order is not a wise column name, since it clashes with a reserved work in all RDBMS.