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

Hello

> On 06.09.2016, at 14:35, Alexander Farber <alexander(dot)farber(at)gmail(dot)com> 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(dot)sandor(at)gmail(dot)com> wrote:
>>> On 6 September 2016 at 14:23, Alexander Farber <alexander(dot)farber(at)gmail(dot)com> wrote:
>>>
>>>> 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
>>>
>>> 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
>
>>> 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