0

I have this table:

ID      dayNum      dayName     eventTypeID     
-------------------------------------------
1       4           Wednesday   1
2       7           Saturday    1

For an eventTypeID I want to select the row that contains the future or current day. Today is Thursday, so how can I return the row where dayName is Saturday?

I just need an AND statement for the below query:

SELECT      *
FROM        `events`
WHERE       `eventTypeID` = 1
AND         ??

dayNum:

The dayNum field is the index for each day, where Monday = 1, and Saturday = 7.

If you can come up with an answer to this, just hard code today's day number as 5 (Thursday). I already have the logic to get today's dayNum, and it's not really relevant to the question.

JJJ
  • 32,902
  • 20
  • 89
  • 102
Drahcir
  • 11,772
  • 24
  • 86
  • 128
  • It sounds like what you want to do is order the grouping so that a specific row is returned for each group. This will involve a subquery and not just an order by. See: http://stackoverflow.com/questions/537223/mysql-control-which-row-is-returned-by-a-group-by – keithhatfield Oct 31 '13 at 17:59
  • Thanks, saved me some time. I edited the question. – Drahcir Oct 31 '13 at 18:02
  • `and dayNUM >= DAYOFWEEK(NOW())` – AgRizzo Oct 31 '13 at 18:07
  • @AgRizzo That only works if there are multiple days for every event, otherwise it could be less than. – Drahcir Oct 31 '13 at 18:17

1 Answers1

1

I would sort by the "difference", then limit 1 like this:

SELECT *
FROM events
ORDER BY MOD(dayNum - DAYOFWEEK(NOW()) + 7, 7)
LIMIT 1

You will need to MOD 7 so if the current day is 7, the next nearest one should be 1 which is equivalent to (-6 MOD 7). There is an extra + 7 on there because MySQL seems to like to keep negative numbers negative after a modulo.

SQLFiddle

Matt Dodge
  • 10,833
  • 7
  • 38
  • 58