title: Is there a [straightforward] way to order results *first*, *then* group by another column, with SQL?
body: <p>I see that in an SQL query, the <code>GROUP BY</code> has to precede the <code>ORDER BY</code> expression. Does this imply that ordering is done after grouping would have discarded identical rows?</p>
  <p>Because I seem to need to order rows by a timestamp first, <em>then</em> discard the rows with identical timestamp. And I don't know how to accomplish this.</p>
  <p>I am using MySQL 5.1.41.</p>
  <p>Here is the definition of the table expressed with <code>create table</code>:</p>
  <pre><code>create table
  (
      A int,
      B timestamp
  )
  </code></pre>
  <p>The data could be:</p>
  <pre><code>+-----+-----------------------+
  |  A  |  B                    |
  +-----+-----------------------+
  |  1  |  today                |
  |  1  |  yesterday            |
  |  2  |  yesterday            |
  |  2  |  tomorrow             |
  +-----+-----------------------+
  </code></pre>
  <p>The results of the query on the above table, which I am after, would be:</p>
  <pre><code>+-----+-----------------------+
  |  A  |  B                    |
  +-----+-----------------------+
  |  1  |  today                |
  |  2  |  tomorrow             |
  +-----+-----------------------+
  </code></pre>
  <p>Basically, I want the rows with the latest timestamp in column &quot;B&quot; (hence the mention of <code>ORDER BY</code>), and only one row for each value in column &quot;A&quot; (think <code>DISTINCT</code> or <code>GROUP BY</code>).</p>
  <h4>The actual problem behind the simplified example above:</h4>
  <p>In reality, I have two tables - <code>users</code> and <code>payment_receipts</code>:</p>
  <pre><code>create table users
  (
      phone_nr int(10) unsigned not null,
      primary key (phone_nr)
  )
  
  create table payment_receipts
  (
      phone_nr int(10) unsigned not null,
      payed_ts timestamp default current_timestamp not null,
      payed_until_ts timestamp not null,
      primary key (phone_nr, payed_ts, payed_until_ts)
  )
  </code></pre>
  <p>The tables may include other columns but I omit these as irrelevant. Implementing a payment scheme, I have to send SMS to users across the cellular network, in periodic intervals depending on whether the payment is due or not. The payment is actualized when the SMS is sent as the recipient is taxed for it. I use the <code>payment_receipts</code> table to keep records of all payments done, i.e. for book-keeping. This is intended to model a real shop where both the buyer and the seller get a copy of the receipt of purchase, for reference. This table stores my (seller's) copy [of each receipt]. The customer's receipt is the received SMS itself. Each time an SMS is sent (and thus a payment is accomplished), the table is inserted a receipt record, stating who paid, when and &quot;until when&quot;. To explain the latter, imagine a subscription service, but one which spans indefinitely until the user opt-out explicitly, at which point the corresponding user record is removed. A payment is made a month in advance, so as a rule, the difference between the <code>payed_ts</code> and <code>payed_until_ts</code> is 30 days worth of time.</p>
  <p>I have a batch job that executes every day and needs to select a list of users that are due monthly payment as part of the automatic subscription renewal described above. To link this to the dummy example earlier, the phone number column <code>phone_nr</code> would be the column &quot;A&quot; and <code>payed_until_ts</code> would be column &quot;B&quot;, but in reality there are two tables, which has to do with the following behaviour: when a user record is removed, the receipt must remain, for book-keeping. So not only do I need to group payments by date and discard all but the latest payment receipt date, I also need to watch out not to select receipts for which there no longer is a matching user record.</p>
  <p>To solve the problem of selecting required records -- those that are due payment -- I need to find receipts with the latest <code>payed_until_ts</code> timestamp for each <code>phone_nr</code> (there may be several, obviously) and out of those records I further need to select only those phone numbers where <code>payed_until_ts</code> is earlier than the time the batch job executes. I then would send an SMS to each of these numbers, inserting a receipt record for each sent SMS, where <code>payed_ts</code> is <code>now()</code> and <code>payed_until_ts</code> is <code>now() + interval 30 days</code>.</p>
  <p>But I can't seem to come up with the query required.</p>