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

On 6 September 2016 at 14:23, Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
wrote:

> Thank you, Sandor -
>
> On Tue, Sep 6, 2016 at 1:30 PM, Sándor Daku <daku(dot)sandor(at)gmail(dot)com> wrote:
>
>>
>> Get the last 6 record and
>>
>> 1. ... action='SKIP' 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='SKIP' then 1 else 0 end) ... If the result
>> is 6 the game ends
>>
>>
> I am trying
>
> SELECT SUM(CASE WHEN action='skip' 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
>
> Regards
> Alex
>
> 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='skip' THEN 1 ELSE 0 END)
FROM words_moves
ORDER BY played DESC
LIMIT 6
INTO _sum

Regards,
Sándor