title: SQLite group-by behaviour
body: <p>If a column in the SELECT clause is omitted from the GROUP BY clause, does SQLite group by the remaining columns (by default), and then return the value of the omitted column in the first row it evaluates?</p>
  
  <p>For example, finding the TransactionId associated with the highest value per ProductId:</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS ProductTransaction
  (
     Id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
     ProductId INTEGER NOT NULL,
     TransactionType INTEGER NOT NULL,
     Value INTEGER NOT NULL
  );
  
  INSERT INTO ProductTransaction (ProductId, TransactionType, Value)
  VALUES (1, 7, 23), (1, 3, 12), (2, 4, 43), (1, 7, 5), (1, 10, 23), 
  (3, 3, 23), (3, 2, 31), (1, 1, 23), (2, 5, 50),  (2, 6, 14), (1, 4, 23);
  
  SELECT ProductId
  , TransactionType
  , MAX(Value)
  FROM ProductTransaction
  GROUP BY ProductId;
  
  DELETE FROM ProductTransaction;
  </code></pre>
  
  <p>Running the previous statements gives me the TransactionType of 7 for ProductId 1 (Highest value 23).  </p>
  
  <p>However, if I add an the index:</p>
  
  <pre><code>CREATE INDEX IF NOT EXISTS IDX_TransType ON ProductTransaction(ProductId ASC, TransactionType ASC);
  </code></pre>
  
  <p>It returns the TransactionType 1, presumably because it's now ordering the rows according to the index.  Modifying the index supports this theory:</p>
  
  <pre><code>CREATE INDEX IF NOT EXISTS IDX_TransType ON ProductTransaction(ProductId ASC, TransactionType DESC);
  </code></pre>
  
  <p>It will now return TransactionType 10 for ProductId 1.</p>
  
  <p>Is this behaviour by design, or is it just an unreliable side-effect?</p>
  
  <p>EDIT:  It seems that it's an unreliable side-effect.  From the documentation:</p>
  
  <blockquote>
    <p>Each expression in the result-set is then evaluated once for each
    group of rows. If the expression is an aggregate expression, it is
    evaluated across all rows in the group. <strong>Otherwise, it is evaluated
    against a single arbitrarily chosen row from within the group</strong>. If
    there is more than one non-aggregate expression in the result-set,
    then all such expressions are evaluated for the same row.</p>
  </blockquote>
  
  <p><a href="https://www.sqlite.org/lang_select.html#resultset" rel="nofollow">https://www.sqlite.org/lang_select.html#resultset</a></p>