I am using SQLite for an Android App.
I have two tables: topic and message. I'd like to select all the topics as well as the number of messages for that topics and the last message of that topic as a single request (using subquery).
I tried the following:
SELECT topic.*, COUNT(message.id), message.* FROM topic
LEFT JOIN (SELECT * FROM message ORDER BY message.ts DESC) AS message
ON topic.id=message.topic_id
GROUP BY topic.id;
This query works perfectly fine when I try it on MySQL on the server side. However, it does not work as expected on the Android App with SQLite: I get all the topics and the right number of messages, but the last message is not the latest one (seems like the ORDER BY in the subquery is of no use).
Any hint?
EDIT: message.ts is a TEXT NOT NULL containing a ts formated as "yyyy-MM-dd HH:mm:ss.SSS". When I query all the messages and order them by ts, the order is fine.