My issue is that I’m stuck on making a select query where I need to select only items from latest 24 hours, that don’t repeat themselves on entries before those 24 hours.
Here is a table:
| name | date |
|---|---|
| Mark | 2021-05-27 |
| Jake | 2021-05-27 |
| Anthony | 2021-05-27 |
| Anthony | 2021-05-26 |
Expected output:
| name |
|---|
| Mark |
| Jake |
Missing element: query
Help will be appreciated.
Edit: I know that’s unprecise. The real table I’m using is with full datetime type. I’m going to test your answers today and then give response.
Advertisement
Answer
’24 hours’ is unprecise, as you do not know which dates of yesterday are actually in range of the past 24 hours. If you are ok only with today’s entries only (which are less), then the following would work:
SELECT name FROM demoTable GROUP BY name HAVING MIN(date) = CURRENT_DATE;
If you actually have date and time available, then you can have:
SELECT name FROM demoTable
GROUP BY name HAVING MIN(datetime) > DATEADD(day, -1, CURRENT_TIMESTAMP);
Depending on SQL dialect, DATEADD(...) might not be available, with e. g. SQLite you’d replace it by DATETIME('now', '-1 day').