Treating (A, B) and (B, A) as same pair and getting max row per pair Following is my table structure:

"id"           = bigint;
"sender_id"    = bigint, FK;
"receiver_id"  = bigint, FK;
"message"      = character varying;
"sent_ts"      = timestamp with time zone;
"delivered_ts" = timestamp with time zone;
"read_ts"      = timestamp with time zone;
"version_no"   = bigint;
"date_created" = timestamp with time zone;
"date_updated" = timestamp with time zone;


For Eg:
If I've following data:

id | sender_id | receiver_id | message | date_created
1  | 1         | 2           | hi      | 2016-05-21
2  | 1         | 2           | hello   | 2016-05-22
3  | 2         | 1           | :)      | 2016-05-23
4  | 1         | 2           | :D      | 2016-05-24
5  | 2         | 1           | hehehe  | 2016-05-25
6  | 1         | 3           | wassup! | 2016-05-26
7  | 3         | 1           | \m/     | 2016-05-27
8  | 3         | 2           | :D:D    | 2016-05-28


I need to find last message record from whichever user (sender, receiver) pair ie considering (1,2) and (2,1) as a single pair.

So, if I pass 2 as sender_id and receiver_id, I need following output:

id | sender_id | receiver_id | message | date_created
5  | 2         | 1           | hehe    | 2016-05-25
8  | 3         | 2           | :D:D    | 2016-05-28


Following is so far what I've tried:

SELECT sender_id, receiver_id, MAX(date_created) date_created 
FROM uconvs 
WHERE sender_id = 2 OR receiver_id = 2
GROUP BY sender_id, receiver_id 
ORDER BY date_created DESC;


Right now I'm getting following output:

sender_id | receiver_id | date_created
1         | 2           | 2016-05-24
2         | 1           | 2016-05-25
3         | 2           | 2016-05-28


I'm unable to consider (1,2) and (2,1) as a single pair and also unable to extract the message as I've used group by.

Let me know if my question is not clear.
I'm using postgresql 9.4+.

What do I need to do to get the above output? I don't know pl/sql much, but if it's the optimal solution that would also do.

Thanks in advance.
First, you can do a lateral cross join to

(VALUES (sender_id), (receiver_id)) AS u (user_id)


That will duplicate each source row but from each duplicate pair you will select only the entry where user_id is not the one specified as the argument:

WHERE
  ...
  AND u.user_id &lt;&gt; 2


Now each row will have a group identifier, which is User 2's counterpart in each row. For your example the row set will look like this:

id | sender_id | receiver_id | message | date_created | user_id
1  | 1         | 2           | hi      | 2016-05-21   | 1
2  | 1         | 2           | hello   | 2016-05-22   | 1
3  | 2         | 1           | :)      | 2016-05-23   | 1
4  | 1         | 2           | :D      | 2016-05-24   | 1
5  | 2         | 1           | hehehe  | 2016-05-25   | 1
8  | 3         | 2           | :D:D    | 2016-05-28   | 3


At this point you could use a MAX(uconv.date_created) OVER (PARTITION BY u.user_id) to get each conversation's last date_created. Once you have obtained it, you can get just the row corresponding to that date.

Here is the full query:

SELECT
  id,
  sender_id,
  receiver_id,
  message,
  date_created
FROM
  (
    SELECT
      *,
      MAX(c.date_created) OVER (PARTITION BY u.user_id) AS last_date
    FROM
      uconv AS c
      CROSS JOIN LATERAL (VALUES (sender_id), (receiver_id)) AS u (user_id)
    WHERE
      2 IN (sender_id, receiver_id)
      AND user_id &lt;&gt; 2
  ) AS derived
WHERE
  date_created = last_date
;


As you can see, this method allows you to include the actual message in the results as well.

Alternatively, you could replace the MAX() OVER () with a ROW_NUMBER() OVER () (and filter on the row number, of course):

SELECT
  id,
  sender_id,
  receiver_id,
  message,
  date_created
FROM
  (
    SELECT
      *,
      ROW_NUMBER() OVER (PARTITION BY u.user_id ORDER BY c.date_created DESC) AS rn
    FROM
      uconv AS c
      CROSS JOIN LATERAL (VALUES (sender_id), (receiver_id)) AS u (user_id)
    WHERE
      2 IN (sender_id, receiver_id)
      AND user_id &lt;> 2
  ) AS derived
WHERE
  rn = 1
;

This is a typical greatest-n-per-group problem. The tricky part was just to determine what to group by.