From: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com> |
---|---|
To: | |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Check if there 6 last records of same type without gaps |
Date: | 2016-09-06 12:23:06 |
Message-ID: | CAADeyWhSsGC-SYZeBC=3PsBvuppQY=G5EsLRwB6mMPxqdV85uQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Sándor Daku | 2016-09-06 12:30:26 | Re: Check if there 6 last records of same type without gaps |
Previous Message | Sándor Daku | 2016-09-06 11:30:59 | Re: Check if there 6 last records of same type without gaps |