CODEWITHSUNDEEP
postgresql
Devil's Dream
Devil's Dream

Reputation: 715

how to resolve group by error query

I am trying to query the following but its not executing

SELECT
  MAX(time) as last_time,
  column_A,
  column_B
from
  table_1
WHERE
  column_c='foo'

Its replying with the following error message

ERROR: column "column_A" must appear in the GROUP BY clause or be used in an aggregate function

However its replying the following query correctly

SELECT MAX(time) as last_time from table_1 WHERE column_c='foo'

What am I doing wrong?

Upvotes: 0

Views: 68

Answers (2)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125204

In the comment in the other answer you say you want only one value:

SELECT
  "time" as last_time,
  column_A,
  column_B
from table_1
WHERE column_c = 'foo'
order by "time" desc
limit 1

Upvotes: 1

Salil
Salil

Reputation: 47472

Ref PostgreSQL 8.0 Group By

All columns that are not in an aggregate function MUST be in the group by clause if there is any column in an aggregate function.

SELECT MAX(time) as last_time, column_A, column_B  
FROM table_1 WHERE column_c='foo' 
GROUP BY column_A, column_B

Upvotes: 1

Related Questions