CODEWITHSUNDEEP
mysqlsql
stumped
stumped

Reputation: 489

Need help with mysql query

I have a table which is randomly generated. It has 3 columns : P_Id, date (at which the row was inserted) and text. Let's say the text can be various animals. So the table could look like

1      2011-06-15 03:04:05    cow  
2      2011-06-15 03:04:15    dog  
3      2011-06-15 03:04:19    bird  
4      2011-06-15 03:04:30    cow  
5      2011-06-15 03:04:30    dog  
6      2011-06-15 03:04:35    cow  
....

What is a query to pick all animals that have occurred at least twice within the last 30 minutes of its latest entry? For eg, here cow occurs 3 times within the last 30 minutes of its latest entry, which is at 3:04:35, and dog has occurred twice. Bird would not be selected as it only occurred once.

Upvotes: 0

Views: 63

Answers (4)

niktrs
niktrs

Reputation: 10056

Assuming that you check the last 30 minutes per event

select name, count(name)
from your_table t1
join (
    select name, max(created_at) created_at
    from your_table t2
) on t1.name = t2.name
where datediff (minute,t1.created_at,t2.created_at)<=30
group by name
having count(name) >= 2

Upvotes: 1

Arun Kumar Arjunan
Arun Kumar Arjunan

Reputation: 6857

Form an inner table called latest_animals that will calculate the latest entry date for each animal. Form another inner table called previous_animals that will calculate the previous latest entry date for each animal.

Do a left join between these 2 tables based on the animal name and filter the record based on the time-diff between the 2 dates.

SELECT latest_animals.animal_name, TIME_DIFF(latest_animals.created_at, previous_animals.created_at) created_diff FROM

(
  SELECT animal_name, MAX(date) created_at FROM animals GROUP BY animal_name
) latest_animals
LEFT JOIN
(
  SELECT animal_name, MAX(date) created_at FROM animals GROUP BY animal_name WHERE id NOT IN
  (SELECT id, MAX(date) FROM animals GROUP BY animal_name)
) previous_animals
ON latest_animals.animal_name =  previous_animals.animal_name
WHERE created_diff > INTERVAL 30 MINUTE

Upvotes: 0

mu is too short
mu is too short

Reputation: 434965

There's always the straight forward way (i.e. use a subquery to extract the timestamp range and then proceed as usual):

select name, count(name)
from your_table
where created_at >= (
    select max(created_at) - INTERVAL 30 MINUTE
    from your_table
)
group by name
having count(name) >= 2

Upvotes: 2

Marcus Leon
Marcus Leon

Reputation: 56709

select animal, count(*) 
from MyTable 
where date >= (currentDate - 30 minutes) 
group by animal
having count(*) >= 2

Upvotes: 0

Related Questions