Как построить SQLite-запрос для GROUP ORDER?

У меня довольно интересная ситуация. У меня есть база данных SQLite, полная адресов и сообщений (адреса не уникальны, сообщения есть). Каждое сообщение также имеет связанную с ним дату. Я хочу, чтобы выбрать первый адрес сообщения, сообщение, дату и количество сообщений, связанных с адресом.

Итак, я подумал: "Я могу просто GROUP по адресу получить только одно сообщение на адрес, затем ЗАКАЗАТЬ их по дате, а также получить COUNT столбца адреса."

Я сделал это, и это работает... любопытно. Он выбирает правильный счет, выбирает только одно сообщение на адрес и заказывает его по дате, но не выбирает последнее сообщение для адреса. Он кажется произвольным.

В качестве примера у меня есть три сообщения (от самых ранних к последним): A, B, C из адреса Y и три сообщения D, E, F из адреса Z. Запрос может получать сообщения B и E, а затем сортировать их по Дата. Он должен получать сообщения C и F и сортировать их по дате.

Вот что я до сих пор:

// Expanded version:
Cursor cursor = db.query(
    /* FROM */ "messages_database",
    /* SELECT */ new String[]{ "*", "COUNT(address) AS count" },
    /* WHERE */ null,
    /* WHERE args */ null,
    /* GROUP BY */ "address",
    /* HAVING */ null,
    /* ORDER BY */ "date DESC"
);

// Or, same code on one line:
Cursor cursor = db.query("messages_database", new String[]{ "*", "COUNT(address) AS count" }, null, null, "address", null, "date DESC");

Мне кажется, что это может иметь отношение к предложению HAVING, но я действительно не знаю. Я много использовал MySQL с PHP, но никогда не приходил в касание HAVING раньше. Я попытался установить мое предложение HAVING на "MAX(date)", но это не повлияло. Если я ставлю GROUP BY предложение "address, date", то они будут отсортированы по дате, но, конечно, все они индивидуальны, а не сгруппированы (поскольку даты отличаются).

Поиски Google оказались бесплодными; такие запросы, как "андроидный sqlite-порядок перед группой" и "андроид-sqlite-группа по заказу", не дают никаких связанных результатов.

Как я могу выбрать последнее сообщение для каждого адреса без удаления моего предложения GROUP (как COUNT() полагается на это)? Мне нужны два запроса?

Изменить: на основе ответа @Adrian связал меня с комментариями, я придумал два запроса, которые оба дали то же самое результат; одна строка, в которой счетчик равен 7 (это общее количество адресов, а не сообщений на адрес), а указанный адрес не относится к последнему сообщению.

Два запроса:

Cursor cursor = db.rawQuery(
      "SELECT t.*, COUNT(t.message_content) AS count "
    + "FROM messages_database t "
    + "INNER JOIN ("
    + "    SELECT address, MAX(date) AS maxdate "
    + "    FROM messages_database "
    + "    GROUP BY address "
    + ") ss ON t.address = ss.address AND t.date = ss.maxdate",
    null
);
Cursor cursor = db.rawQuery(
      "SELECT t1.*, COUNT(t1.message_content) AS count "
    + "FROM messages_database t1 "
    + "LEFT OUTER JOIN messages_database t2 "
    + "ON (t1.address = t2.address AND t1.date < t2.date) "
    + "WHERE t2.address IS NULL",
    null
);

Ответ 1

SQLite имеет расширение, которое делает greatest-n-per-group проблемы намного проще:
Если вы используете агрегированные функции MAX() или MIN(), и если вы одновременно выбираете другие столбцы, не используя их в агрегатной функции или группируя их, тогда получаемые значения для этих столбцов гарантированно будут выходить той же записи, которая имеет максимальное/минимальное значение. (Это не разрешено в других диалектах SQL и было представлено в SQLite 3.7.11.)

Итак, для вашей проблемы вы можете использовать такой запрос:

SELECT *, COUNT(address) AS count, MAX(date)
FROM messages_database
GROUP BY address

Если у вас нет SQLite 3.7.11 (что, скорее всего, на большинстве версий Android) или с использованием другого механизма SQL, следующий запрос будет работать:

SELECT *,
       (SELECT COUNT(address) AS count
        FROM messages_database m2
        WHERE m1.address = m2.address)
FROM messages_database m1
WHERE date = (SELECT MAX(date)
              FROM messages_database m3
              WHERE m1.address = m3.address)
GROUP BY address

Ответ 2

Решил! В итоге я использовал комбинацию @CL. и методы, описанные в моем отредактированном сообщении (уточнено в этом ответе, отправленный @Adrian).

Поскольку я не хотел использовать 3 SELECT (как описано в @CL. answer), я использовал ту же концепцию INNER JOIN, что и в других утверждениях, сохраняя при этом свою методологию.

В результате получится следующее:

Cursor cursor = db.rawQuery(
      "SELECT t.*, ss.count AS count "
    + "FROM messages_database t "
    + "INNER JOIN ("
    + "    SELECT address, MAX(date) AS maxdate, COUNT(address) AS count "
    + "    FROM messages_database "
    + "    GROUP BY address "
    + ") ss ON t.address = ss.address AND t.date = ss.maxdate "
    + "GROUP BY t.address "
    + "ORDER BY t.date DESC ",
    null
);

И он отлично работает!