title: How to construct a SQLite query to GROUP by ORDER?
body: <p>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 <em>the first</em> message's address, message, date, and <em>how many messages</em> are associated with the address.</p>
  
  <p>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."</p>
  
  <p>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 <em>most recent</em> message for the address. It appears to be arbitrary.</p>
  
  <p>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, <em>then</em> sort them by date. It <em>should</em> fetch messages C and F, and sort those by date.</p>
  
  <p>Here is what I have so far:</p>
  
  <pre><code>// 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");
  </code></pre>
  
  <p>I feel like this may have to do with the <code>HAVING</code> clause, but I really don't know. I've used MySQL a lot with PHP, but never had to touch <code>HAVING</code> before. I tried setting my <code>HAVING</code> clause to <code>"MAX(date)"</code>, but it had no effect. If I set my <code>GROUP BY</code> clause to be <code>"address, date"</code>, then they are sorted by date, but of course they are all individual instead of grouped (since the dates differ).</p>
  
  <p>Google searches have proved fruitless; queries like "<em>android sqlite order before group</em>" and "<em>android sqlite group by order</em>" yield no related results.</p>
  
  <p>How can I select the <em>one latest</em> message for each address without removing my <code>GROUP</code> clause (as <code>COUNT()</code> relies upon this)? Do I need two queries?</p>
  
  <p><strong>Edit</strong>: Based on <a href="https://stackoverflow.com/a/7745635/570191">the answer</a> @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 <em>per</em> address), and the address shown was not that of the latest message.</p>
  
  <p>The two queries were:</p>
  
  <pre><code>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 &lt; t2.date) "
      + "WHERE t2.address IS NULL",
      null
  );
  </code></pre>