Re: Check if there 6 last records of same type without gaps - Mailing list pgsql-general
From | Charles Clavadetscher |
---|---|
Subject | Re: Check if there 6 last records of same type without gaps |
Date | |
Msg-id | 4461770F-F046-463A-B7CB-B0037A571C9B@swisspug.org Whole thread Raw |
In response to | Re: Check if there 6 last records of same type without gaps (Alexander Farber <alexander.farber@gmail.com>) |
Responses |
Re: Check if there 6 last records of same type without gaps
(Alexander Farber <alexander.farber@gmail.com>)
|
List | pgsql-general |
Hello
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@gmail.com> wrote:On 6 September 2016 at 14:23, Alexander Farber <alexander.farber@gmail.com> wrote:On Tue, Sep 6, 2016 at 1:30 PM, Sándor Daku <daku.sandor@gmail.com> wrote:Get the last 6 record and1. ... 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 endsSELECT 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 CASCADESry! I wasn't clear enough.Those are two separate solutions. Pick one!In this case you don't need the group bySELECT SUM(CASE WHEN action='skip' THEN 1 ELSE 0 END)
FROM words_moves
ORDER BY played DESC
LIMIT 6
INTO _sum
pgsql-general by date: