SQL: How to select multiple records per day, assuming that each day contain more than 1 value, MySQL

Question

So I have a huge table which contains multiple records from each day. I would like to get like any 5 or 10 records from everyday. Each row has created_at field, timestamp format example: 2015-01-01 00:00:01. And every row has a unique ID field.

Using MySQL 5.5.44

I found this (SQL: How to select one record per day...):

select * from value_magnitudes
where id in 
(
    SELECT min(id)
    FROM value_magnitudes
    WHERE magnitude_id = 234
    and date(reading_date) >= '2013-04-01'
    group by date(reading_date)
)

It gives me one record from each day. I tried several times to modify that for my needs but I guess it can't be done with that simple query.

Then I found this (How to select more than 1 record per day?):

SELECT date_time, other_column
FROM  (
   SELECT *, row_number() OVER (PARTITION BY date_time::date) AS rn
   FROM   tbl
   WHERE  date_time >= '2012-11-01 0:0'
   AND    date_time <  '2012-12-01 0:0'
   ) x
WHERE  rn < 4;

Can't get this one working (changed the name of the field of course). It gives me this error:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(PARTITION BY created_at::date) AS rn FROM tbl WHERE created_at >= '201' at line 3

So could you guys and gals point me to the right direction, please.

Update: Desired result would look something like this (only two from same date, but you get the idea):

id                                      company_id                          created_at
0001c73d-8f95-49b8-a380-275c3863974d    90f87a37-801f-4f05-b86d-b25574aa457d    2015-12-01 22:36:20
000074d3-0116-4d13-bea9-7256d522ea88    53171f70-7b65-4e17-a222-8d9952351b88    2015-12-01 14:09:19
00002a24-c14a-4584-8657-ad62a62d8bb2    ad83edbc-fe82-443f-96fb-8188b91fdd62    2015-12-02 09:15:22
000023b7-9a32-4ac9-9f8d-e18712c0a940    3713bc34-bffa-458d-98bc-266acfcacc6c    2015-12-02 14:57:24
0000d51e-dea7-446c-9974-4536501d77ec    23d1556c-0719-4260-9740-a8882badf2db    2015-12-03 13:19:22
0020e450-866f-4467-b5fe-892d1b9565a0    90f87a37-801f-4f05-b86d-b25574aa677d    2015-12-03 13:46:01
00001bc4-6d39-494c-8b4c-43050ebe537e    90f87a37-801f-4f05-b86d-b25574aa478d    2015-12-04 22:09:50
000035cf-409f-4cfb-adcd-412eb5def840    90f87a37-801f-4f05-b86d-b25574aa434d    2015-12-04 22:50:43

INSERT for that would be like:

INSERT INTO `invoices` (`id`, `company_id`, `created_at`)
VALUES
    ('0001c73d-8f95-49b8-a380-275c3863974d', '90f87a37-801f-4f05-b86d-b25574aa457d', '2015-12-01 22:36:20'),
    ('000074d3-0116-4d13-bea9-7256d522ea88', '53171f70-7b65-4e17-a222-8d9952351b88', '2015-12-01 14:09:19'),
    ('00002a24-c14a-4584-8657-ad62a62d8bb2', 'ad83edbc-fe82-443f-96fb-8188b91fdd62', '2015-12-02 09:15:22'),
    ('000023b7-9a32-4ac9-9f8d-e18712c0a940', '3713bc34-bffa-458d-98bc-266acfcacc6c', '2015-12-02 14:57:24'),
    ('0000d51e-dea7-446c-9974-4536501d77ec', '23d1556c-0719-4260-9740-a8882badf2db', '2015-12-03 13:19:22'),
    ('0020e450-866f-4467-b5fe-892d1b9565a0', '90f87a37-801f-4f05-b86d-b25574aa677d', '2015-12-03 13:46:01'),
    ('00001bc4-6d39-494c-8b4c-43050ebe537e', '90f87a37-801f-4f05-b86d-b25574aa478d', '2015-12-04 22:09:50'),
    ('000035cf-409f-4cfb-adcd-412eb5def840', '90f87a37-801f-4f05-b86d-b25574aa434d', '2015-12-04 22:50:43');

If you like, consider following this simple two-step course of action: 1. If you have not already done so, provide proper CREATE and INSERT statements (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. — Strawberry, Dec 23 '15 at 09:53
Updated desired result and insert for that. Hope people get the idea. — Taapsa, Dec 23 '15 at 16:10

score 0 · Accepted Answer · answered Dec 23 '15 at 10:05

0

You can use variable, to get resultset rownumbers since mysql doesn't support ROWNUM as oracle.

Please try this :

select * 
from (
    select id,date(date_time) as date_time,other_columns,
    case when @date = date(date_time) then 
        @rn := @rn + 1 
    else 
        @rn := 1 
    end as x, -- row numbers for each date_time
    @date := date(date_time)
    from your_table
    inner join(select @date := '', @rn:=1) as tmp
    order by date(date_time) asc
) as tbl
where x <= 5 --5 records for each date_time

The query above will give you 5 records (if any) for each day.

answered Dec 23 '15 at 10:05

Hotdin Gurning

1,821
3
15
24

I guess this could work but I'm working on a read only slave so I can' write tmp. – Taapsa Dec 23 '15 at 10:33
@Taapsa Sorry, what do you mean with *I'm working on a read only slave so I can't write tmp* – Hotdin Gurning Dec 23 '15 at 10:43
When I run that query I get error: Error writing file '/tmp/MYhvHybc' (Errcode: 28) – Taapsa Dec 23 '15 at 11:15
Or what I'm doing wrong here. I changed the date_time to match my table = created_at and I assume that I can give desired timespan in here: " inner join(select @date := ' ' ''? – Taapsa Dec 25 '15 at 12:21
@Taapsa No, @date is variable, you can't give desired timespan there. you can add *where clause* in the subquery `...) as tmp where create_at between :from and :to order by date(date_time)...` – Hotdin Gurning Dec 26 '15 at 03:38
Of course, silly me... After I added that where clause it is working like a charm, thanks a lot! – Taapsa Dec 27 '15 at 10:32

SQL: How to select multiple records per day, assuming that each day contain more than 1 value, MySQL

1 Answers1