mysql limited count per day

Question

SELECT user_id, date_time, journal_id FROM logs WHERE user_id = 1

user_id |       date_time     | journal_id
   1   | 2014-02-04 11:15:00 |     21
   1   | 2014-02-04 11:25:00 |     22
   1   | 2014-02-04 14:00:00 |     31
   1   | 2014-02-05 10:15:00 |     33
   1   | 2014-02-05 12:55:00 |     45
   1   | 2014-02-05 15:15:00 |     48
   1   | 2014-02-05 15:35:00 |     49
   1   | 2014-02-06 05:00:00 |     62
   1   | 2014-02-06 13:40:00 |     67
   1   | 2014-02-06 15:00:00 |     72

I would like to select the first 3 rows for each date (*note that the 5th of feb. has 4 rows), and I was thinking if its do-able with count or limit, but without any luck. I have been looking for solutions, but havent had any luck. Mostly I would really like to know if it's do-able with MySQL, or if I just have to do some date checking in PHP instead, to remove the unwanted rows from the result.

The actual result I'm aiming for would be this:

user_id |       date_time     | journal_id
   1   | 2014-02-04 11:15:00 |     21
   1   | 2014-02-04 11:25:00 |     22
   1   | 2014-02-04 14:00:00 |     31
   1   | 2014-02-05 10:15:00 |     33
   1   | 2014-02-05 12:55:00 |     45
   1   | 2014-02-05 15:15:00 |     48
   1   | 2014-02-06 05:00:00 |     62
   1   | 2014-02-06 13:40:00 |     67
   1   | 2014-02-06 15:00:00 |     72

Any ideas?

if i understand correctly, you wants to display like first four rows in that table... — jmail, Mar 17 '14 at 09:43
Yes. It's do-able with MySQL. Solutions to this problem are widely discussed - here and elsewhere. Someone will no doubt provide another one in a minute. — Strawberry, Mar 17 '14 at 09:48

score 1 · Answer 1 · answered Mar 17 '14 at 10:08

Working solution:

select user_id, date_time, journal_id from (
  select 
    user_id, date_time, journal_id
    , @crid:=
      (case when @pdt=(@cdt:=date_format(date_time, '%Y-%m-%d')) 
                 then (@crid+1) 
                 else (@pdt:=@cdt)/@pdt 
       end) as grouped_row_num
  from logs, ( select @crid:=0, @cdt:=0, @pdt:=0 ) r
) results
where grouped_row_num <= 3
;

score 0 · Answer 2 · answered Mar 17 '14 at 10:11

You may want to try the following

Create a stored procedure where the cursor is the date
Select the rows from the table with a limit and copy those results to a new table

An example how i would make it:

CREATE PROCEDURE SHOW_FIRS_ROWS (IN TABLENAME VARCHAR(50)) DECLARE varSQL VARCHAR(255); DECLARE varDATE VARCHAR(100); DECLARE no_more_rows INT DEFAULT 0; DECLARE cursor1 CURSOR FOR SELECT DISTINCT DATE(date_time) FROM logs; DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = 1; OPEN cursor1; FETCH cursor1 INTO varDATE; REPEAT SET @varSQL = CONCAT("INSERT INTO newTable SELECT * FROM logs WHERE DATE(date_time) = ", varDATE ," LIMIT 0,3"); PREPARE stmt FROM @varSQL; EXECUTE stmt; FETCH cursor1 INTO varDIR; UNTIL no_more_rows = 1 END REPEAT; CLOSE cursor1; END

mysql limited count per day

2 Answers2