I have got table for example
+------+--------+--------+---------------------+
| Type | Value1 | Value2 | DateAdded |
+------+--------+--------+---------------------+
| 1 | a | a | 2014-03-31 20:00:00 |
| 2 | a | a | 2014-03-31 20:00:10 |
| 3 | a | a | 2014-03-31 20:00:25 |
| 1 | a | a | 2014-03-31 20:00:40 |
| 2 | a | a | 2014-03-31 20:00:50 |
| 3 | a | a | 2014-03-31 20:00:60 |
| 1 | a | a | 2014-03-31 20:01:10 |
| 2 | a | a | 2014-03-31 20:01:25 |
| 3 | a | a | 2014-03-31 20:01:35 |
+------+--------+--------+---------------------+
and many more rows... I want to to recieve in a single query:
and there can be more than three types.
One approach to this type of query is the count(*)
in a subquery approach:
select *
from table t
where 20 >= (select count(*)
from table t2
where t2.type = t.type and
t2.DateAdded >= t.DateAdded
);
Count the number of rows with the same time that have a larger DateAdded
and choose the ones that have up to 20 of them.