sql statement to return based on what is the last row based on a where clause

Question

I have two table as below

match

id (PK)
match_id
name
birthdate
bio

message_long

id (PK)
message
from_id
to_id
match_id (FK to match_on on match table)
unix_timestamp

enter image description here

These two tables are responsible for storing chat conversation logs. I can retreive a conversation by the following sql query

select * from message_log where match_id = '2434';

I would like to come up with an sql query that would return the match_ids of conversations only if:

The last message (last message received) in the conversation (match_id) is not from '765' (from_id column)

Can someone please help me construct this sql query?

We very much prefer *text* to screenshots. Text can be copy/pasted. Plus, as always: your version of Postgres? — Erwin Brandstetter, Jun 17 '15 at 05:25
I agree with Erwin. Ideally table definitions should be posted as `create table` statements. That way it's easy to setup a test environment. — , Jun 17 '15 at 05:43

score 0 · Answer 1 · edited May 23 '17 at 12:29

0

The last message (last message received) in the conversation (match_id) [which] is not from '765' (from_id column)

Seems like a straight forward query:

SELECT *
FROM   message_log
WHERE  match_id = '2434'
AND    from_id <> '765'
ORDER  BY unix_timestamp DESC NULLS LAST
LIMIT  1;

If unix_timestamp can be NULL, you need to add NULLS LAST to DESC sort order:

PostgreSQL sort by datetime asc, null first?

If, on the other hand, you mean to return all match_id's of a conversation only if the the last message is not from '765', then:

SELECT *
FROM   message_log
WHERE  match_id = '2434'
AND (
   SELECT from_id
   FROM   message_log
   WHERE  match_id = '2434'
   ORDER  BY unix_timestamp DESC NULLS LAST
   LIMIT  1
   ) <> '765';

An index on (match_id, unix_timestamp) or even (match_id, unix_timestamp, from_id) would help query performance a lot.

Aside: Data type varchar(500) for all your IDs is rather odd. Are you sure these shouldn't be just integer (or bigint)?

edited May 23 '17 at 12:29

Community

1
1

answered Jun 17 '15 at 05:31

Erwin Brandstetter

605,456
145
1,078
1,228

1

I think you have missed `return the match_ids of conversations only if`. OP wants to return the result only if `The last message (last message received) in the conversation (match_id) [which] is not from '765' (from_id column)` – Nandakumar V Jun 17 '15 at 05:54
I am not sure though.. thats what I understood – Nandakumar V Jun 17 '15 at 05:55
@NandakumarV: Hmm, you may be right. I added a query for the second interpretation. – Erwin Brandstetter Jun 17 '15 at 06:03
What i understood was entirely different, I have created the query from what i understood, http://stackoverflow.com/a/30883911/1418007 – Nandakumar V Jun 17 '15 at 06:40

score 0 · Answer 2 · answered Jun 17 '15 at 06:36

Not sure if I have understood the question correctly. As I understood you need,

All match ids whose last converstion is not been originated from 765.

If we consider a sample data

matchid fromid  toid    messagetime
2334    600     765     2015-06-17 11:30:36
2334    765     600     2015-06-17 11:30:43
2334    600     765     2015-06-17 11:30:52
2335    600     765     2015-06-17 11:37:44
2335    765     600     2015-06-17 11:37:56
2335    600     765     2015-06-17 11:38:06
2335    765     600     2015-06-17 11:48:03

you want the match id 2334. match id 2335 should be omitted since the last conversation is from 765.

If this is what you need this query will get the result

SELECT resultmatchids FROM 
(
    SELECT 
    DISTINCT ON (match_id) match_id,
    (CASE WHEN from_id != 765 THEN match_id ELSE NULL END) AS resultmatchids
    FROM match 
    ORDER BY match_id, unix_timestamp DESC
) AS recentmatch
WHERE recentmatch.resultmatchids IS NOT NULL

sql statement to return based on what is the last row based on a where clause

2 Answers2