0

I have a table of events, each with someone in charge. There may be multiple of these events per day, but I need a query record of the first for each user on a given day.

For example, if I have the following table of events:

+----------+-------------+---------------------+
| event_id | director_id | event_start         |
+----------+-------------+---------------------+
|        1 |         111 | 2015-04-27 10:00:00 |
+----------+-------------+---------------------+
|        2 |         222 | 2015-04-27 11:00:00 |
+----------+-------------+---------------------+
|        3 |         333 | 2015-04-27 12:00:00 |
+----------+-------------+---------------------+
|        4 |         111 | 2015-04-27 13:00:00 |
+----------+-------------+---------------------+
|        5 |         222 | 2015-04-27 09:00:00 |
+----------+-------------+---------------------+

I would like the following returned:

+----------+-------------+---------------------+
| event_id | director_id | event_start         |
+----------+-------------+---------------------+
|        1 |         111 | 2015-04-27 10:00:00 |
+----------+-------------+---------------------+
|        5 |         222 | 2015-04-27 09:00:00 |
+----------+-------------+---------------------+
|        3 |         333 | 2015-04-27 12:00:00 |
+----------+-------------+---------------------+

I thought a query like the following would have worked, but it turns out that MySQL does not support MIN in the WHERE clause (simple SQL query giving Invalid use of group function):

SELECT
        event_id, director_id, MIN(event_start) AS event_start 
    FROM events 
    WHERE MIN(event_start) >= '2015-04-27 00:00:00' 
        AND MIN(event_start) < '2015-04-28 00:00:00'
    GROUP BY director_id;

How can I do this in the most efficient way possible? My events table may easily have 10,000-100,000 records.

Community
  • 1
  • 1
Bing
  • 3,071
  • 6
  • 42
  • 81
  • Why dont you use `WHERE event_start = '2015-04-27 00:00:00'`? Because `value >= 1 AND value < 2` results in `value = 1`!? – C4d Apr 27 '15 at 05:33
  • Follow this link : http://stackoverflow.com/questions/17938384/mysql-min-in-where-clause – Sagar Apr 27 '15 at 05:34
  • @C4ud3x The events could start at any given time. The first isn't certain to start at midnight. – Bing Apr 27 '15 at 05:34
  • Oh Im sorry. There is the time itself for sure hah. Was just looking for the date. – C4d Apr 27 '15 at 05:35
  • shouldnt event_id 5, director_id 222 have an event_start of `2015-04-27 09:00:00` in your required output? – pala_ Apr 27 '15 at 05:38

3 Answers3

2

You can get the minimum event time on each day with a query similar to yours:

SELECT director_id, date(event_start) as dte, MIN(event_start) AS event_start 
FROM events e
GROUP BY director_id, date(event_start);

You can then use this as a subquery to get all other information from the row:

select e.*
from events e join
     (SELECT e.director_id, date(e.event_start) as dte, MIN(e.event_start) AS event_start 
      FROM events e
      GROUP BY e.director_id, date(e.event_start)
     ) ee
     on e.event_start = ee.event_start -- note, this has both the date and time;

If you want to restrict the results to a single day, you can put the where clause in the subquery.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

You cant use group by / aggregate functions in the where clause of a query. One way to do what you want is to use a left join like so:

select e1.*
  from events e1
    left join events e2
      on e1.director_id = e2.director_id
        and e1.event_start > e2.event_start
        and date(e1.event_start) = date(e2.event_start)
  where e2.director_id is null

fiddle here

Performance is likely to be increased if you have an index across (director_id, event_start)

You can also further limit the result size by changing and date(e1.event_start) = date(e2.event_start) to check for specific dates.

pala_
  • 8,901
  • 1
  • 15
  • 32
0

You can give this a try:

SELECT
  e1.* 
FROM events AS e1
  INNER JOIN ( SELECT director_id, MIN(event_start) AS `eventStart` 
               FROM `events` GROUP BY director_id ) AS e2
  ON e1.director_id = e2.director_id
  AND e1.event_start = e2.eventStart
WHERE e2.eventStart >= '2015-04-27 00:00:00' 
  AND e2.eventStart < '2015-04-28 00:00:00';

Here is the sqlfiddle.

Abhishekh Gupta
  • 6,206
  • 4
  • 18
  • 46
  • no. you can't group by director_id, and then arbitrarily include event_id in your select fields. – pala_ Apr 27 '15 at 05:36