Re: Check if there 6 last records of same type without gaps

[Alexander Farber <alexander.farber@xxxxxxxxx>]

No, I am sorry - for struggling with probably basic questions, but without GROUP BY I get another error:

org.postgresql.util.PSQLException: ERROR: column "words_moves.played" must appear in the GROUP BY clause or be used in an aggregate function| 
Where: PL/pgSQL function words_skip_game(integer,integer) line 27 at SQL statement

On Tue, Sep 6, 2016 at 2:30 PM, Sándor Daku <daku.sandor@xxxxxxxxx> wrote:

On 6 September 2016 at 14:23, Alexander Farber <alexander.farber@xxxxxxxxx> wrote:

On Tue, Sep 6, 2016 at 1:30 PM, Sándor Daku <daku.sandor@xxxxxxxxx> wrote:

Get the last 6 record and 

1.  ... action="" as isskip ... then you can group on and count the skip moves. If there is 6 of them the game ends.

2.  ... sum(case when action="" then 1 else 0 end) ... If the result is 6 the game ends

        SELECT SUM(CASE WHEN action="" THEN 1 ELSE 0 END)
        FROM words_moves
        GROUP BY action
        ORDER BY played DESC
        LIMIT 6
        INTO _sum;

        RAISE NOTICE '_sum = %', _sum;

        IF _sum = 6 THEN
                _finished = CURRENT_TIMESTAMP;
        END IF;

but get the error -

org.postgresql.util.PSQLException: ERROR:
column "words_moves.played" must appear in the GROUP BY clause or be used in an aggregate function| 
Where: PL/pgSQL function words_skip_game(integer,integer) line 27 at SQL statement

 

P.S: Here is the table in question

                                  Table "public.words_moves"
 Column |           Type           |                         Modifiers
--------+--------------------------+-----------------------------------------------------------
 mid    | integer                  | not null default nextval('words_moves_mid_seq'::regclass)
 action | words_action             | not null
 gid    | integer                  | not null
 uid    | integer                  | not null
 played | timestamp with time zone | not null
 tiles  | jsonb                    |
 score  | integer                  |
Indexes:
    "words_moves_pkey" PRIMARY KEY, btree (mid)
Check constraints:
    "words_moves_score_check" CHECK (score > 0)
Foreign-key constraints:
    "words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON DELETE CASCADE
    "words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON DELETE CASCADE
Referenced by:
    TABLE "words_games" CONSTRAINT "words_mid_fk" FOREIGN KEY (mid) REFERENCES words_moves(mid)
    TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY (mid) REFERENCES words_moves(mid) ON DELETE CASCADE

Sry! I wasn't clear enough.

Those are two separate solutions. Pick one!

In this case you don't need the group by 

        SELECT SUM(CASE WHEN action="" THEN 1 ELSE 0 END)
        FROM words_moves
        ORDER BY played DESC
        LIMIT 6
        INTO _sum