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?

CodePudding user response：

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 :

see dbfiddle

CodePudding user response：

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.