MySQL query to select group of items in every month

Question

For example i have the following table:

items
-----
item_id    (usigned-int, not-null, auto-increment, primary)
created_at (datetime, not-null)
...

And i have the following dataset:

(1, 01/01/2012)
(2, 03/01/2012)
(3, 07/01/2012)
(4, 12/01/2012)
(5, 28/01/2012)
(6, 02/02/2012)
(7, 06/02/2012)
(8, 13/02/2012)
(9, 27/02/2012)

I would like to get the following result:

(3, 07/01/2012)
(4, 12/01/2012)
(5, 28/01/2012)
(8, 13/02/2012)
(9, 27/02/2012)

i.e. every first (n = two) records in every month are skipped.

How can i achieve this with MySQL?

Answer 1

It's select based on rank of an item within partition:

create table items (
    item_id    int,
    created_at datetime
);


insert into items
select 1, '2012-01-01' union
select 2, '2012-01-03' union
select 3, '2012-01-07' union
select 4, '2012-01-12' union
select 5, '2012-01-28' union
select 6, '2012-02-02' union
select 7, '2012-02-06' union
select 8, '2012-02-13' union
select 9, '2012-02-28'


select i.*
from items i
join (
  select t.item_id, count(item_id1) as row_no
  from (
    select t.item_id, t1.item_id as item_id1
    from items t
    join items t1 on t.item_id >= t1.item_id and 
      DATE_FORMAT(t.created_at, '%Y%m') = DATE_FORMAT(t1.created_at, '%Y%m')
  ) t
  group by t.item_id
) g on i.item_id = g.item_id
where g.row_no > 2

Live example on SQL Fiddle.

Answer 2

Try this query -

SELECT id, created_at FROM (
  SELECT t1.*, COUNT(*) r FROM items t1
    LEFT JOIN items t2
      ON MONTH(t2.created_at) = MONTH(t1.created_at) AND t2.id <= t1.id
  GROUP BY
    MONTH(t1.created_at), t1.id
  ) t
WHERE r > 2;

You also need to filter by year, or just add year conditions -

SELECT id, created_at FROM (
  SELECT t1.*, COUNT(*) r FROM items t1
    LEFT JOIN items t2
      ON YEAR(t2.created_at) = YEAR(t1.created_at) AND
         MONTH(t2.created_at) = MONTH(t1.created_at) AND
         t2.id <= t1.id
  GROUP BY
    YEAR(t1.created_at), MONTH(t1.created_at), t1.id
  ) t
WHERE r > 2;