Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux