Stack Exchange
Limit rows of every month?
I need to get data between Decemember 2012 to November 2014.
Each month I only need 1500 rows. 
For example:
SELECT * FROM data WHERE YEAR(submit_date) = 2012 AND MONTH(submit_date) = 12 limit 1500;
SELECT * FROM data WHERE YEAR(submit_date) = 2013 AND MONTH(submit_date) = 1 limit 1500;
SELECT * FROM data WHERE YEAR(submit_date) = 2013 AND MONTH(submit_date) = 2 limit 1500;
SELECT * FROM data WHERE YEAR(submit_date) = 2013 AND MONTH(submit_date) = 3 limit 1500;
and until Nov 2014

Is there a way to write SQL query smaller?

You might want to check out the heading Select the top N rows from each group in this article which addresses your problem. In short it suggests a loop in the SQL.

There are some options list here: http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/
IMHO one of the best is using a row-counter:
set @num := 0, @type := '';

select id, name, submit_date,
  @num := if(@type = CONCAT(YEAR(submit_date), MONTH(submit_date)), @num + 1, 1) as row_number,
  @type := CONCAT(YEAR(submit_date), MONTH(submit_date)) as dummy
from data force index(IX_submit_date)
group by id, name, submit_date
having row_number <= 2;

You can test it here: http://sqlfiddle.com/#!2/e829c/13 (I do a cut for 2 elements, not for 1500)

Thanks! What is force index(IX_submit_date) ? Seem like adding index?

Yes for a better performance of the DBMS. You don't need it, but I would suggest it.

In your SQL Query, how to specify  submit_date between Decemember 2012 to November 2014 only?

You can do this with where submit_date between http://sqlfiddle.com/#!2/e829c/17 . For testing with dataset I used startdate 2012-05-01 ... just use 2012-12-01

Thanks. I have having a problem. I don't want to group anything and data that are duplicated should be showed.

Like this: select number, outcome, submit_date,   @num := if(@type = CONCAT(YEAR(submit_date), MONTH(submit_date)), @num + 1, 1) as row_number,   @type := CONCAT(YEAR(submit_date), MONTH(submit_date)) as dummy from data   WHERE submit_date >= '2012-12-01' AND submit_date <= '2014-11-31' having row_number <= 5 order by submit_date

I think you're looking for a GROUP BY clause. I would need to know a bit more to give you a definitive answer. But the following pseduo-query might guide you in the right direction.
SELECT *, SUM(some_field) 
FROM data
GROUP BY MONTH(submit_date)

Or if you only need 1500 rows, select the top 1500 ordered by the date
SELECT TOP(1500) *
FROM data
WHERE submit_date > '12-01-2012' AND submit_date < '11-01-2014'
ORDER BY MONTH(submit_date)

With MySQL you can use LIMIT
SELECT *
FROM data
WHERE submit_date > '12-01-2012' AND submit_date < '11-01-2014'
ORDER BY MONTH(submit_date)
LIMIT 0,1500;


That wouldn't work, see my SQL query as example. Each month I want 1500 rows only. Example: WHERE YEAR(submit_date) = 2013 AND MONTH(submit_date) = 3 limit 1500;

For example, 1500 rows for Jan 2013, 1500 rows for Feb Jan 2013 and so on until until Nov 2014

I updated my post to reflect your question more directly, but thanks for overly harsh down vote. You need to refine your question by showing your schema. You can't fault me when you only give me a sample.

Sorry I didnt mean to... anyway I don't think TOP() is working on mySQL

You can do it almost like you have it, just add a UNION between your queries. But you still have to create 1 query per month.
Otherwise you need to enumerate the rows that are returned. You need to first order and enumerate your records, then you can do a select on that select to get only the top X. Not sure if you want to include the last month or not.
SET @prev_date='';
SELECT * FROM (
SELECT IF(@prev_date=submit_date, @incr := @incr+1, @incr:=1) AS row_num, 
data.*, 
(@prev_date := submit_date) AS set_prev_date 
FROM data WHERE submit_date BETWEEN "2012-12-01" AND "2014-11-30" 
ORDER BY submit_date
) tmp WHERE row_num<1500;

