Workaround for illegal alias in a GROUP BY clause

Question

I want to find the number of consecutive records (in chronological order, according to a field [when]) matching a condition (here data > 10). Thanks to this answer I arrived at this:

SELECT tt.i, COUNT(*) AS count
FROM  t tt WHERE data > 10
GROUP BY i, (SELECT COUNT(*) FROM t WHERE [when] < tt.[when] AND NOT data > 10 AND i=tt.i );

I also need the results to be sorted chronologically, and although it looks like GROUP BY sorts them automatically, it does not seem guaranteed, so I need to add an ORDER BY clause:

SELECT tt.i, COUNT(*) AS count
FROM  t tt WHERE data > 10
GROUP BY i, (SELECT COUNT(*) FROM t WHERE [when] < tt.[when] AND NOT data > 10 AND i=tt.i )
ORDER BY i, (SELECT COUNT(*) FROM t WHERE [when] < tt.[when] AND NOT data > 10 AND i=tt.i );

This works, but is not good due to subquery repetition. So I want to use an alias:

SELECT tt.i, COUNT(*) AS count, (SELECT COUNT(*) FROM t WHERE [when] < tt.[when] AND NOT data > 10 AND i=tt.i ) AS xid
FROM  t tt WHERE data > 10
GROUP BY i, xid
ORDER BY i, xid;

This works too, but I do not want xid to be in my results; I put it in the SELECT just to be aliased. Furthermore, I do not understand why this works, since according to this answer GROUP BY is executed before the SELECT, hence should no have access to the alias xid.

Here is my test table, in case one would like to try the query out:

CREATE TABLE t(i INT , [when] DATETIME, data INT);
INSERT INTO t(i, [when], data) VALUES (1, '20130813', 1);
INSERT INTO t(i, [when], data) VALUES (2, '20130812', 121);
INSERT INTO t(i, [when], data) VALUES (1, '20130811', 132);
INSERT INTO t(i, [when], data) VALUES (2, '20130810', 15);
INSERT INTO t(i, [when], data) VALUES (1, '20130809', 9);
INSERT INTO t(i, [when], data) VALUES (2, '20130808', 1435);
INSERT INTO t(i, [when], data) VALUES (1, '20130807', 143);
INSERT INTO t(i, [when], data) VALUES (2, '20130806', 18);
INSERT INTO t(i, [when], data) VALUES (1, '20130805', 19);
INSERT INTO t(i, [when], data) VALUES (2, '20130804', 1);
INSERT INTO t(i, [when], data) VALUES (1, '20130803', 1234);
INSERT INTO t(i, [when], data) VALUES (2, '20130802', 124);
INSERT INTO t(i, [when], data) VALUES (1, '20130801', 6);

1. How could I make my query correct (ensure a correct order), avoiding both repeating the subquery and outputting xid in the results?
2. Why does my last query work in spite of the clause execution order?

Answer 1

To remove columns from the final result, use a subquery:

SELECT only, what, I, want
FROM (SELECT ...);

The SQL standard specifies that aliases from the SELECT clause are not available in the GROUP BY clause to allow that specific execution order. However, SQLite does not enforce all restrictions from the standard, and if it actually finds a definition for xid, it just uses it. But when a standard-conforming interpretation and some other one conflict, SQLite tends to choose the former; consider the result of this query:

SELECT a AS b
FROM (SELECT 1 AS a, 'x' AS b UNION ALL
      SELECT 2,      'y'      UNION ALL
      SELECT 3,      'x')
GROUP BY b;