How to construct a SQLite query to GROUP by ORDER?

Question

I've got a rather interesting situation. I have a SQLite database full of addresses and messages (addresses are not unique; messages are). Each message also has a date associated with it. What I want to do is select the first message's address, message, date, and how many messages are associated with the address.

So, I thought, "I can just GROUP by the address to only get one message per address, then ORDER these by the date, and also fetch the COUNT of the address column."

I did that, and it works... kinda. It fetches the correct count, fetches only one message per address, and orders them by date--but it does not select the most recent message for the address. It appears to be arbitrary.

As an example, I have three messages (earliest to latest) A, B, C from address Y, and three messages D, E, F from address Z. The query may fetch messages B and E, then sort them by date. It should fetch messages C and F, and sort those by date.

Here is what I have so far:

// 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");

I feel like this may have to do with the HAVING clause, but I really don't know. I've used MySQL a lot with PHP, but never had to touch HAVING before. I tried setting my HAVING clause to "MAX(date)", but it had no effect. If I set my GROUP BY clause to be "address, date", then they are sorted by date, but of course they are all individual instead of grouped (since the dates differ).

Google searches have proved fruitless; queries like "android sqlite order before group" and "android sqlite group by order" yield no related results.

How can I select the one latest message for each address without removing my GROUP clause (as COUNT() relies upon this)? Do I need two queries?

Edit: Based on the answer @Adrian linked me to in the comments, I came up with two queries which both produced the same result; one row, in which the count was 7 (which is the total number of addresses, not messages per address), and the address shown was not that of the latest message.

The two queries were:

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
);

It appears you have a [tag:greatest-n-per-group] problem on your hands. Read here, it will most likely help you: http://stackoverflow.com/a/7745635/570191 — Adriano Carneiro, Sep 26 '12 at 18:25
@Adrian I've checked out that answer and tried to incorporate those ideas. However, they simply seem to provide one row, containing a count of a different column. I've posted my queries above, as well as a more detailed result of what didn't work. — Cat, Sep 26 '12 at 18:54

CL. · Accepted Answer · 2012-09-26T19:10:38.747

16

SQLite has an extension that makes greatest-n-per-group problems much easier:
If you are using the MAX() or MIN() aggregate functions, and if you are selecting other columns at the same time without using them in an aggregate function or grouping by them, then the resulting values for those columns are guaranteed to come out of the same record that has the maximum/minimum value. (This is not allowed in other SQL dialects, and was introduced in SQLite 3.7.11.)

So, for your problem, you can use a query like this:

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

If you don't have SQLite 3.7.11 (which is likely on most Android versions) or using another SQL engine, the following query will work:

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

edited Sep 26 '12 at 19:10

answered Sep 26 '12 at 18:56

CL.

173,858
17
217
259

I'm afraid this doesn't seem to work, either. It appears to have the same result as my initial attempt (though the rows come out in a different order). – Cat Sep 26 '12 at 19:01
Ah, with your edit, and @Adrian's contribution, I managed to get it working. I've accepted your answer as you led me to the solution, and posted my working solution below. Thanks! – Cat Sep 26 '12 at 19:27
what if you are sorting on multiple fields... if you use a `MAX()` on each sort field are you guaranteed to get the correct result? – Michael Jun 17 '14 at 15:51
When one MAX matches on one row, and another MAX on another, what should the result be? This does not make sense in the first place. – CL. Jun 17 '14 at 20:06

score 7 · Answer 2 · edited May 23 '17 at 12:06

Solved it! I ended up using a combination of @CL.'s method and the methods I outlined in my edited post (clarified in this answer, posted by @Adrian).

Because I didn't want to use 3 SELECT statements (as @CL.'s answer described), I used the same INNER JOIN concept as in the other statements, while retaining his methodology.

The result is this:

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
);

And it's working perfectly!

How to construct a SQLite query to GROUP by ORDER?

2 Answers2