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

Postgresql – How to select rows based on properties of another row?

Had a question..

| a_id | name | r_id | message        | date
_____________________________________________
|  1   | bob  | 77   | bob here       | 1-jan
|  1   | bob  | 77   | bob here again | 2-jan
|  2   | jack | 77   | jack here.     | 2-jan
|  1   | bob  | 79   | in another room| 3-feb
|  3   | gill | 79   | gill here      | 4-feb

These are basically accounts (a_id) chatting inside different rooms (r_id)

I’m trying to find the last chat message for every room that jack a_id = 2 is chatting in.
What i’ve tried so far is using distinct on (r_id) ... ORDER BY r_id, date DESC.

But this incorrectly gives me the last message in every room instead of only giving the last message in everyroom that jack belongs to.

|  2   | jack | 77   | jack here.     | 2-jan
|  3   | gill | 79   | gill here      | 4-feb

Is this a partition problem instead distinct on?

Tags:

2

Answers


  1. 0

    I would suggest :

    • to group the rows by r_id with a GROUP BY clause
    • to select only the groups where a_id = 2 is included with a HAVING clause which aggregates the a_id of each group : HAVING array_agg(a_id) @> array[2]
    • to select the latest message of each selected group by aggregating its rows in an array with ORDER BY date DESC and selecting the first element of the array : (array_agg(t.*))[1]
    • to convert the selected rows into a json object and then displaying the expected result by using the json_populate_record function

    The full query is :

    SELECT (json_populate_record(null :: my_table, (array_agg(to_json(t.*)))[1])).*
  FROM my_table AS t
 GROUP BY r_id
 HAVING array_agg(a_id) @> array[2]

    and the result is :

    a_id name r_id message date
    1 bob 77 bob here 2022-01-01

    see dbfiddle

    Login or Signup to reply.
  2. 0

    For last message in every chat room simply would be:

    select a_id, name, r_id,  to_char(max(date),'dd-mon')  from chats 
where a_id =2
group by r_id, a_id,name;

    Fiddle https://www.db-fiddle.com/f/keCReoaXg2eScrhFetEq1b/0

    Or seeing messages

    with last_message as (
select a_id, name, r_id, to_char(max(date),'dd-mon') date  from chats 
where a_id =1
group by r_id, a_id,name 
)
select l.*, c.message 
from last_message l 
join chats c on (c.a_id= l.a_id and l.r_id=c.r_id and l.date=to_char(c.date,'dd-mon'));

    Fiddle https://www.db-fiddle.com/f/keCReoaXg2eScrhFetEq1b/1

    Though all this complication could by avoided with a primary key on your table.

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

Back To Top
Search