CODEWITHSUNDEEP
sqlpostgresql
Andrus
Andrus

Reputation: 27975

How to fix column must appear in group by clause error in sql select

Is postgres , sql script

create temp table dok ( dokumnr serial primary key, kuupaev date not null ) on commit drop;
create temp table omdok ( dokumnr serial primary key, kuupaev date not null ) on commit drop;
create temp table omrid ( dokumnr int references omdok, tasudjrk int references dok ) on commit drop;

      select dok.dokumnr ,
    ( 
    SELECT MAX(kuupaev)
     from 
    (select MAX(omdok.kuupaev)
           from omdok
            join omrid using (dokumnr)
           where omrid.tasudjrk=dok.dokumnr
    limit 1
    ) x
    ) as maxtasu
        from DOK

causes error

ERROR:  column "dok.dokumnr" must appear in the GROUP BY clause or be used in an aggregate function
LINE 7:    select dok.dokumnr ,
                  ^

********** Error **********

ERROR: column "dok.dokumnr" must appear in the GROUP BY clause or be used in an aggregate function
SQL state: 42803
Character: 330

How to fix this ?

Upvotes: 0

Views: 88

Answers (1)

D-Shih
D-Shih

Reputation: 46249

You used MAX witch is a aggregate function,so need to add group by.

You could try this.

select dok.dokumnr ,
( 
    SELECT MAX(kuupaev)
    from 
    (
        select MAX(omdok.kuupaev)
        from omdok
        join omrid using (dokumnr)
        where omrid.tasudjrk=dok.dokumnr
        limit 1
    ) x
) as maxtasu
from DOK 
group by dok.dokumnr

Upvotes: 2

Related Questions