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]

 



Hello

On 06.09.2016, at 14:35, Alexander Farber <alexander.farber@xxxxxxxxx> 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.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