Take 1 record per date, without repeating the date

Asked

Viewed 39 times

-3

I have a box system where every time the box is opened and closed every day is recorded on the table, the box is opened and closed several times a day, however I would like to catch only the first opening of the day and with that list the last 30 days showing only the first opening of each day.

How to mount a select to work this way? I’ve tried limit, top1 none of it worked for me.

Thank you.

mysql sql

  • puts the sql you already have ready

1 answer

0

First you select the Day (Date without time) and then you use the MIN function on the date and time, grouping by day. Example:

select
    cast(c.data as date) as dia,
    min(c.data) as abertura
from caixas c
group by cast(c.data as date);

Upshot: 

dia         abertura
2017-05-01  2017-05-01T10:41:23Z
2017-05-02  2017-05-02T18:40:28Z
2017-05-03  2017-05-03T18:40:31Z

  • gave to understand, and when the field is with schedule? there is some solution without need to touch in my current structure?

  • Note that I used the same field in both columns, and there are times... I used the cast to convert to date that eliminates the timetable....

  • taking advantage, it has not to do with the answer, but rather with the question. Go to the [Tour] to see how to ask better and not receive these negative votes. When an answer solves your problem, mark it as well!

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.