0

I've got a Database in MySQL with around 1,000,000 DateTime Entries (Format: 2022-01-31 19:45:39) and so on.

For each day I have 100 Entries. I need a query to get the first DateTime Entry of the day, for the last 30 Days, where Column Number = 1234.

In PostgreSQl I would use a Lateral Join, but how to deal with it in MySQL?

Schema:

id           int(11) auto_increment
datetime     datetime   
number       varchar(255)

Someone can give me a full working example? I can't get it working

Denis49
  • 71
  • 8
  • What is your schema? https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query – kmoser Jan 31 '22 at 19:15
  • id int(11) Auto-Inkrement datetime datetime number varchar(255) – Denis49 Jan 31 '22 at 19:16
  • Does this answer your question? [Retrieving the last record in each group - MySQL](https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql) – sticky bit Jan 31 '22 at 19:26
  • Not really, how would I group them? I mean I can get the last 30 Days like: 'DATE_SUB(CURDATE(),INTERVAL 30 DAY)' <= 'datetime' but then I have records of 30 days and still can't get the first of each day. – Denis49 Jan 31 '22 at 19:32
  • The common day groups them, doesn't it? – sticky bit Jan 31 '22 at 19:33
  • What I've tried was "group by datesub(curdate()) order by datetime limit 1" but thats not working at all. – Denis49 Jan 31 '22 at 19:35
  • `GROUP BY LEFT(datetime, 10)` to group by individual days. – kmoser Jan 31 '22 at 19:58

0 Answers0