mysql - SQL Query Multiple Inner Joins with Multiple Orders

Question

I've got an SQL query (MySQL / InnoDB) with multiple inner joins.

The query works fine in ordering the queries table results in ascending order, however I cannot seem to get it to return the latest message row from the messages table as well.

The query is as follows:

select a.id query_id
     , q.reference query_reference
     , q.status query_status
     , q.a_guid query_a_guid
     , q.created_at query_created_at
     , q.updated_at query_updated_at
    , c.id contacts_id
    , c.msisdn contacts_msisdn
    , m.id messages_id
    , m.messageId messages_messageId
    , m.created_at messages_created_at
    , m.timestamp messages_timestamp
    , m.dir messages_dir
    , m.content messages_content
    , m.data messages_data
    , p.msisdn profiles_msisdn
  from queries q
  join contacts c
    on c.id = q.contactID
  join profiles p 
    on p.id = c.profileID
  join messages m 
    on q.contactID = m.contactID
where q.status = ?
    and (q.a_guid is null or q.a_guid = ?)
    and m.dir = ? 
 group 
    by m.contactID
 order 
    by q.updated_at ASC
     , m.id DESC

The objective is to return the earliest queries (ASC) with the latest message (DESC).

I'm certain this can be achieved through sub-queries, however the inner join statements seem to produce the fastest results, as the queries and messages tables contain quite a number of rows.

I've also tried putting the messages order results before the queries order results, however to no avail.

question from:https://stackoverflow.com/questions/65945918/sql-query-multiple-inner-joins-with-multiple-orders

Answer 1

Waitting for answers