nenad007 nenad007 - 1 month ago 11
MySQL Question

MySQL check if key value exist more then n times with different date

How I can check if a value occur in my table 2 or more times, but it must be on two diffferent dates.
I don't need to know how much times it occur, I only must know if the passed key occur two or more times.

As the table holds many records, I'm looking for a way that in best case not all reccords must be scanned.

id | key | request_datetime
1 | 111 | 05-05-2017
2 | 222 | 06-05-2017
3 | 111 | 06-05-2017
4 | 111 | 06-05-2017
5 | 222 | 06-05-2017


If I check for the key 111 i should get a indicator that is true, if I check for 222 i should not get any back or a indicator for false.

So far I have tried to group by key and request_datetime then count the rows, the performance was too bad this way.

Answer Source

Your question literally translates to a query which aggregates the keys and counts the number of distinct request dates for each key. In the query below I retain only keys having the same request date appearing 3 or more times, but you can change the threshold to whatever you want.

SELECT
    `key`
FROM yourTable
GROUP BY `key`
HAVING COUNT(DISTINCT request_datetime) > 2   -- or whatever value you want

Note that I had to place the column name key into backticks as it is a MySQL reserved keyword. You should avoid naming your tables and columns with keywords unless absolutely necessary.