samedi 27 février 2016

Workaround for illegal alias in a GROUP BY clause

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?

Aucun commentaire:

Enregistrer un commentaire