Select last timestamp from database with JOIN query

Question

I have a query that selects groups and group postings. In the postings table there is a timestamp collumn, and a group_id. I need to select the greatest timestamp in the postings table, associated with the group id.

Here's my query

$query = "
    SELECT groups.group_name, groups.group_info, groups.group_tags, postings.timestamp 
    FROM groups 
    LEFT JOIN postings ON groups.group_id = postings.group_id 
    GROUP BY groups.group_id 
    ORDER BY `postings`.`timestamp` DESC
";

Answer your question: [http://stackoverflow.com/questions/4687827/mysql-group-by-with-order-by-desc][1] [1]: http://stackoverflow.com/questions/4687827/mysql-group-by-with-order-by-desc — bvarga, Mar 31 '13 at 00:34

score 0 · Accepted Answer · edited Mar 31 '13 at 00:31

0

$query = "
    SELECT groups.group_name, groups.group_info, groups.group_tags, max(postings.timestamp) 
    FROM groups 
    LEFT JOIN postings ON groups.group_id = postings.group_id 
    GROUP BY groups.group_id 
    ORDER BY `postings`.`timestamp` DESC
";

edited Mar 31 '13 at 00:31

ba0708

10,180
13
67
99

answered Mar 31 '13 at 00:23

Nik Drosakis

2,258
21
30

Nice work, I wasn't sure if max would do it, but next time I'll go with my instinct. Thanks! – Mar 31 '13 at 00:25
1

Keep walking! Instinct is powerful! – Nik Drosakis Mar 31 '13 at 00:26

Select last timestamp from database with JOIN query

1 Answers1