query not working need orderby before groupby?

This is supposed to be a list of topics which have new attachments posted to them.

The group by in this query isn't working as I would like it to. (It's SMF.) It's supposed to pull one representative attachment from each thread that has recent attachments – not just new threads but any thread. The results would be one thumbnail picture linking to the thread where the respective new image is posted.

Instead it doesn't work. It currently has 2 problems. First problem is that it shows the first attachment in the thread instead of the most recent. The second problem is that it orders by thread creation date. I want attachment creation date to be the order and the list of threads is sorted based on which threads have the newest attachments in them. Thus an old thread doesn't rise to the top even if it has recent attachments. Do I need a sub select to order by first? How can I make this work?

Thank you so much in advance.

SELECT *
FROM smf_attachments AS att
    INNER JOIN smf_messages AS m ON (m.id_msg = att.id_msg)
    INNER JOIN smf_topics AS t ON (t.id_topic = m.id_topic)
    LEFT JOIN smf_members AS mem ON (mem.id_member = m.id_member)
    LEFT JOIN smf_attachments AS thumb ON (thumb.id_attach = att.id_thumb)
WHERE att.attachment_type = 0
    AND t.id_board = 3

GROUP BY t.id_topic         
ORDER BY att.id_attach  DESC
LIMIT 2

(Edit.)

Say we have three topic_id each with 5 attachments. Higher attachment number means it's a newer attachment, and higher topic_id means it's a newer topic.

topic_id 1|attachment 3,6,13,14,15
topic_id 2|attachment 1,2,4,5,12
topic_id 3|attachment 7,8,9,10,11

In the above case the query should pull 2 records (limit 2):

topic_id  attachment
--------  ----------
1         15
2         12

Since I only want 1 attachment per topic, the 2 topics with newest attachments are topic 1 and 2 (with attachment 15 and 12 respectively). Even though topic 3 is more recent it wouldn't be a result in this case as it's attachments are older.

(Edit.)

I tried the solution provided by systemmatrix but it's still sorting by topic_id creation date instead of which topics contain newest attachments. I tried to make my question above more clear. Any help? Thank you in advance.

(edit) I now see that it's sorting by date of the first attachment in each topic. topics by most recent first attachment. I need topics with most recent attachment or last attachment

Edit I made a change that is giving me the right list of topics. Here is the new code.

            SELECT att.id_thumb, att.id_msg, att.attachment_type, att.id_attach, distinct on (t.id_topic)
            FROM smf_attachments AS att
                INNER JOIN smf_messages AS m ON (m.id_msg = att.id_msg)
                INNER JOIN smf_topics AS t ON (t.id_topic = m.id_topic)
                LEFT JOIN smf_members AS mem ON (mem.id_member = m.id_member)
                LEFT JOIN smf_attachments AS thumb ON (thumb.id_attach = att.id_thumb)
            WHERE att.attachment_type = 0
                AND t.id_board = 3


            ORDER BY max(att.id_attach)  DESC
            LIMIT 2

Now the only question is how do I get it to show me the right thumbnail attachment? It's still showing the first attachment in the respective topic instead of the last. any help? Thank you in advance.

Solution

If these are the only 3 columns you need the recent records per topic

att.id_thumb
att.id_msg
att.attachment_type

You can use the trick of using SUBSTRING_INDEX over the result of GROUP_CONCAT like to get the most recent id_thumb for the topic ordered by id_attach you can write your expression for id_thumb as

SUBSTRING_INDEX(
GROUP_CONCAT(att.id_thumb ORDER BY att.id_attach DESC SEPARATOR '||'),
'||',1) AS id_thumb

GROUP_CONCAT(expr)

This will group all the id_thumbs for a topic and provide you the list separated by double || or you can change the separator as per your needs in above i have use || for e.g result for above function will be like thumb1||thumb2||thumb3||thumb4 this will be ordered by id_attach in descending way.

Note The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet.

SUBSTRING_INDEX(str,delim,count)

From docs Returns the substring from string str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. SUBSTRING_INDEX() performs a case-sensitive match when searching for delim.

Now your final query will look like as below

SELECT 
SUBSTRING_INDEX(GROUP_CONCAT(att.id_thumb ORDER BY att.id_attach DESC SEPARATOR '||'),'||',1) AS id_thumb,
SUBSTRING_INDEX(GROUP_CONCAT(att.id_msg ORDER BY att.id_attach DESC SEPARATOR '||'),'||',1) AS id_msg,
SUBSTRING_INDEX(GROUP_CONCAT(att.attachment_type ORDER BY att.id_attach DESC SEPARATOR '||'),'||',1) AS attachment_type,
MAX(att.id_attach) AS id_attach_max,
t.id_topic
FROM
  smf_attachments AS att 
  INNER JOIN smf_messages AS m ON (m.id_msg = att.id_msg) 
  INNER JOIN smf_topics AS t ON (t.id_topic = m.id_topic) 
  LEFT JOIN smf_members AS mem ON (mem.id_member = m.id_member) 
  LEFT JOIN smf_attachments AS thumb ON (thumb.id_attach = att.id_thumb) 
WHERE att.attachment_type = 0 
  AND t.id_board = 3 
GROUP BY  t.id_topic
ORDER BY id_attach_max DESC 
LIMIT 2