CODEWITHSUNDEEP
mysqlsql
Iqaru
Iqaru

Reputation: 97

How to select only entries that didn't repeat before a specific date?

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.

Upvotes: 0

Views: 287

Answers (3)

Akina
Akina

Reputation: 42651

SELECT DISTINCT t1.name
FROM tablename t1
WHERE t1.`date` = CURRENT_DATE
  AND NOT EXISTS ( SELECT NULL
                   FROM tablename t2
                   WHERE t1.name = t2.name
                     AND t2.`date` < CURRENT_DATE );

Upvotes: 1

Aconcagua
Aconcagua

Reputation: 25526

'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').

Upvotes: 1

Rahul Shukla
Rahul Shukla

Reputation: 716

Use the below query to get the last 24 data without duplicate name

select name from tabl1 where date>getdate()-1 group by name having count(name)>0

Here getdate()-1 will give the last 1 day difference i.e. 24hr

Upvotes: 1

Related Questions