skip to Main Content

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

2

Answers


  1. Chosen as BEST ANSWER

    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. 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