sqlselectmulti-query

SQL select id from a table to query again all at once

Given the following table:

id message        owner_id counter_party_id datetime_col
1  "message 1"    4        8                2014-04-01 03:58:33
2  "message 2"    4        12               2014-04-02 10:27:34
3  "message 3"    4        8                2014-04-03 09:34:38
4  "message 4"    4        12               2014-04-06 04:04:04

How to get the most recent counter_party number and then get all the messages from that counter_party id?

output:

2  "message 2"    4        12            2014-04-02 10:27:34
4  "message 4"    4        12            2014-04-06 04:04:04

I think a double select must work for that but I don't know exactly how to perform this.

Thanks

Solution

  • Should be the last message so either max(id) or latest datetime in this case, counter_party_id is just an user id the most recent counter_party_id does not mean the max counter_party_id(I found the solution in the answers and I gave props):

    SELECT * 
FROM yourTable 
WHERE counter_party_id = ( SELECT MAX(id) FROM yourTable )

    or 

    SELECT * 
FROM yourTable 
WHERE counter_party_id = ( SELECT counter_party_id FROM yourTable ORDER BY m.time_send DESC LIMIT 1)

    Reason being is that I simplified the example but I had to implement this in a much more complicated scheme.