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);
- How could I make my query correct (ensure a correct order), avoiding both repeating the subquery and outputting
xid
in the results? - Why does my last query work in spite of the clause execution order?
Aucun commentaire:
Enregistrer un commentaire