title: Select Earliest Date and Time from List of Distinct User Sessions
body: <p>I have a table of user access sessions which records website visitor activity:</p>
  
  <p><code>accessid, userid, date, time, url</code></p>
  
  <p>I'm trying to retrieve all distinct sessions for userid 1234, as well as the earliest date and time for each of those distinct sessions.</p>
  
  <pre><code>SELECT 
      DISTINCT accessid, 
      date, 
      time 
  FROM 
      accesslog 
  WHERE userid = '1234' 
  GROUP BY accessid
  </code></pre>
  
  <p>This gives me the date and time of a random row within each distinct accessid. I've read a number of posts recommending the use of min() and max(), so I tried:</p>
  
  <pre><code>SELECT DISTINCT accessid, MIN(DATE) AS date, MIN(TIME) AS time FROM accesslog WHERE userid = '1234' GROUP BY accessid ORDER BY date DESC, time DESC
  </code></pre>
  
  <p>... and even...</p>
  
  <pre><code>SELECT DISTINCT accessid, MIN(CONCAT(DATE, ' ', TIME)) AS datetime FROM accesslog WHERE userid = '1234' GROUP BY accessid ORDER BY date DESC, time DESC
  </code></pre>
  
  <p>... but I never get the correct result of the earliest date and time.</p>
  
  <p>What is the trick to ordering this kind of query?</p>
  
  <p>EDIT -</p>
  
  <p><b>Something weird is happening....</b></p>
  
  <p>The code posted below by Bill Karwin correctly retrieves the earliest date and time for sessions that started in 2009-09. But, for sessions that began on some day in 2009-08, the time and date for the first hit occurring in the current month is what is returned. In other words, the <b>query does not appear to be spanning months!</b></p>
  
  <p>Example data set:</p>
  
  <pre><code>accessid    | userid    | date          | time
  1           | 1234      | 2009-08-15    | 01:01:01
  1           | 1234      | 2009-09-01    | 12:01:01
  1           | 1234      | 2009-09-15    | 13:01:01
  2           | 1234      | 2009-09-01    | 14:01:01
  2           | 1234      | 2009-09-15    | 15:01:01
  </code></pre>
  
  <p>At least on my actual data table, the query posted below finds the follow <em>earliest</em> date and time for each of the two accessid's:</p>
  
  <pre><code>accessid    | userid    | date          | time
  1           | 1234      | 2009-09-01    | 12:01:01
  2           | 1234      | 2009-09-01    | 14:01:01
  </code></pre>
  
  <p>... and I would guess that the only reason the result for accessid 2 appears correct is because it has no hits in a previous month.</p>
  
  <p>Am I going crazy?</p>
  
  <p>EDIT 2 -</p>
  
  <p>The answer is yes, I am going crazy. The query works on the above sample data when placed in a table of duplicate structure.</p>
  
  <p>Here is the (truncated) original data. I included the very first hit, another hit in the same month, the first hit of the next month, and then the last hit of the month. The original data set has many more hits in between these points, for a total of 462 rows.</p>
  
  <pre><code>accessid                            | date          | time
  cbb82c08d3103e721a1cf0c3f765a842    | 2009-08-18    | 04:01:42
  cbb82c08d3103e721a1cf0c3f765a842    | 2009-08-23    | 23:18:52
  cbb82c08d3103e721a1cf0c3f765a842    | 2009-09-17    | 05:12:16
  cbb82c08d3103e721a1cf0c3f765a842    | 2009-09-18    | 06:29:59
  </code></pre>
  
  <p>... the query returns the 2009-09-17 value as the earliest value when the original table is queried. But, when I copy the ........ oh, balls. </p>
  
  <p><b>It's because the hits from 2009-08% have an empty <code>userid</code> field.</b></p>