PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Column m.id must appear in the GROUP BY clause or be used in an aggregate?

Have the following messages table:

ID OWNER TO MESSAGE DATE
1 y x Hello 1
2 y x World 2
3 z x Postgres 3

And users table:

ID USERNAME
x x
y y
z z

Need to get data as conversations for TO, but only one message sorted by date desc, so the result is two columns of ids 2 and 3

select
    m.id,
    u.id as from_id,
    u.username as from_username,
    m.message,
    m.date
from messages as m
join users as u on u.id = m.owner
where m.to = x
group by m.owner
order by m.date desc

Expected result:

ID FROM_ID FROM_USERNAME MESSAGE DATE
2 y y World 2
3 z z Postgres 3
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Found solution by using select distinct on (m.owner) and removed group by and added m.owner in order by and worked as expected. So is as follows:

    select distinct (m.owner)
    m.id,
    u.id as from_id,
    u.username as from_username,
    m.message,
    m.date
from messages as m
join users as u on u.id = m.owner
where m.to = x
order by m.owner, m.date desc

  2. 0

    Use ROW_NUMBER to get the latest message per owner

       WITH uniqueMessages AS (
      SELECT id,
             owner,
             to,
             message,
             date,
             ROW_NUMBER() OVER(PARTITION BY owner ORDER BY dateDESC ) rn
       FROM messages 
       WHERE to= 'x'
    ) 
    SELECT um.id,
           um.owner,
           um.to,
           um.message,
           um.date
    FROM uniqueMessages um 
    INNER JOIN users u on u.id = m.owner
    WHERE rn = 1;

    As per the error

    It reflects the general rule: the columns listed in SELECT should appear in GROUP BY. If they don’t appear in GROUP BY, then they have to be used in the aggregate function. One case is excluded if primary key is included in the group by clause there is no need to add the remaining columns on the select list because primary key uniquely identifies every row on the table.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search