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

[Charles Clavadetscher <clavadetscher@xxxxxxxxxxxx>]

Hello

On 06.09.2016, at 14:35, Alexander Farber <alexander.farber@xxxxxxxxx> wrote:

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

You must group by played, as the message suggests. You are implicitly selecting the column through order by, although you don't have it in the list of selected columns.

Charles

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