First of all, please excuse the language, I have been having trouble parsing my problem into actual English so if anyone could edit to make it clearer that would help.
I have been struggling for some time with this one. I am in need of a query that, per each group, from the last N days, skips the most recent one and retrieves the next T days. This is a version of the classic 'LIMIT with GROUP' problem, and in fact, one of the queries that I tried that didn't work, used that form.
MRE as follows:
CREATE TABLE `trying` (id INTEGER PRIMARY KEY AUTO_INCREMENT, types1 TEXT, stuffs INTEGER, dates DATE);
INSERT INTO `trying`(types1, stuffs, dates) VALUES("one",123,'2015-09-06');
INSERT INTO `trying`(types1, stuffs, dates) VALUES("one",67,'2015-09-05');
INSERT INTO `trying`(types1, stuffs, dates) VALUES("one",45,'2015-09-04');
INSERT INTO `trying`(types1, stuffs, dates) VALUES("one",98,'2015-09-03');
INSERT INTO `trying`(types1, stuffs, dates) VALUES("one",89,'2015-09-02');
INSERT INTO `trying`(types1, stuffs, dates) VALUES("two",56,'2015-09-02');
INSERT INTO `trying`(types1, stuffs, dates) VALUES("two",34,'2015-09-01');
INSERT INTO `trying`(types1, stuffs, dates) VALUES("two",98,'2015-08-31');
INSERT INTO `trying`(types1, stuffs, dates) VALUES("two",34,'2015-08-30');
INSERT INTO `trying`(types1, stuffs, dates) VALUES("two",12,'2015-08-29');
INSERT INTO `trying`(types1, stuffs, dates) VALUES("three",3,'2015-09-06');
INSERT INTO `trying`(types1, stuffs, dates) VALUES("three",8,'2015-09-04');
INSERT INTO `trying`(types1, stuffs, dates) VALUES("three",80,'2015-09-02');
INSERT INTO `trying`(types1, stuffs, dates) VALUES("three",9,'2015-09-01');
INSERT INTO `trying`(types1, stuffs, dates) VALUES("three",6,'2015-08-31');
In table trying there are three types1 'types': 'one', 'two' and 'three', and there are 5 observations per group. Note that the dates are not similar between the groups and there might even be gaps between them (so no dates BETWEEN, like in this question).
In this example I would like to get a table with the three middle values per group. So skip the first and last value, the expected output would look like this:
types1 stuffs dates
one 67 2015-09-05
one 45 2015-09-04
one 98 2015-09-03
two 34 2015-09-01
two 98 2015-08-31
two 34 2015-08-30
three 8 2015-09-04
three 80 2015-09-02
three 9 2015-09-01
A couple of queries that did not work:
SELECT types1, stuffs, dates FROM trying GROUP BY types1 LIMIT 2,4;
/*this returned the following */
types1 stuffs dates
two 56 2015-09-02
SELECT trying.* FROM (SELECT types1, stuffs, dates FROM trying) GROUP BY trying.types1 OFFSET 2,4;
/*threw out an error: Every derived table must have its own alias */
select types1,stuffs,dates from (
select @rank:=if(@prev_cat=types1,@rank+1,1) as rank,
types1,stuffs,dates,@prev_cat:=types1
from trying,(select @rank:=0, @prev_cat:="")t
order by types1, dates desc
) temp
where rank between 2 and 4