SQL alternative to sub-query in FROM

I have a table containing user to user messages. A conversation has all messages between two users. I am trying to get a list of all the different conversations and display only the last message sent in the listing.

I am able to do this with a SQL sub-query in FROM.

CREATE TABLE `messages` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `from_user_id` bigint(20) DEFAULT NULL,
 `to_user_id` bigint(20) DEFAULT NULL,
 `type` smallint(6) NOT NULL,
 `is_read` tinyint(1) NOT NULL,
 `is_deleted` tinyint(1) NOT NULL,
 `text` longtext COLLATE utf8_unicode_ci NOT NULL,
 `heading` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `created_at_utc` datetime DEFAULT NULL,
 `read_at_utc` datetime DEFAULT NULL,
 PRIMARY KEY (`id`)
);


SELECT * FROM
 (SELECT * FROM `messages` WHERE TYPE = 1 AND
 (from_user_id = 22 OR to_user_id = 22)
 ORDER BY created_at_utc DESC
 ) tb
GROUP BY from_user_id, to_user_id;

SQL Fiddle:
http://www.sqlfiddle.com/#!2/845275/2

Is there a way to do this without a sub-query?
(writing a DQL which supports sub-queries only in 'IN')

Solution

You seem to be trying to get the last contents of messages to or from user 22 with type = 1. Your method is explicitly not guaranteed to work, because the extra columns (not in the group by) can come from arbitrary rows. As explained in the [documentation][1]:

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Sorting of the result set occurs after values have been chosen, and ORDER BY does not affect which values within each group the server chooses.

The query that you want is more along the lines of this (assuming that you have an auto-incrementing id column for messages):

select m.*
from (select m.from_user_id, m.to_user_id, max(m.id) as max_id
      from message m
      where m.type = 1 and (m.from_user_id = 22 or m.to_user_id = 22)
     ) lm join
     messages m 
     on lm.max_id = m.id;

Or this:

select m.*
from message m
where m.type = 1 and (m.from_user_id = 22 or m.to_user_id = 22) and
      not exists (select 1
                  from messages m2
                  where m2.type = m.type and m2.from_user_id = m.from_user_id and
                        m2.to_user_id = m.to_user_id and
                        m2.created_at_utc > m.created_at_utc
                 );

For this latter query, an index on messages(type, from_user_id, to_user_id, created_at_utc) would help performance.