help building sql query to get only the next immediate result

[jeffmc21]

Have a table, similar to a sports schedule, called 'schedule'. I need to query the table to return on the next event based on the date.

The call I was trying to use is:

SELECT DATE_FORMAT(game_date,'%a, %b %d') as game_date FROM `schedule` WHERE `game_date` >= CURDATE() ORDER BY game_date LIMIT 1

The problem is that although today is July 31 and the events start on Aug 28 and run weekly, the call is returning results from the end of October, which are actually towards the end of the schedule.

Any ideas on how to refine the call to get the very next date after the Current Date?

[oesxyl]

Have a table, similar to a sports schedule, called 'schedule'. I need to query the table to return on the next event based on the date.

The call I was trying to use is:

SELECT DATE_FORMAT(game_date,'%a, %b %d') as game_date FROM `schedule` WHERE `game_date` >= CURDATE() ORDER BY game_date LIMIT 1

The problem is that although today is July 31 and the events start on Aug 28 and run weekly, the call is returning results from the end of October, which are actually towards the end of the schedule.

Any ideas on how to refine the call to get the very next date after the Current Date?

try:

... order by game_date desc limit 1


regards

[guelphdad]

the problem you are running into is you are using the same name for your alias as the column name. give it a different alias and then order by your game_date field.

[jeffmc21]

Tried the first suggestion of 'order by game_date desc limit 1' and it did return a result closer to the correct row, but was still mid September rather than late August.


Gonna try the second suggestion, I'm assuming that this is the alias:

SELECT DATE_FORMAT(game_date,'%a, %b %d') as game_date FROM `schedule` WHERE `game_date` >= CURDATE() ORDER BY game_date LIMIT 1

[guelphdad]

Exactly.

Right now your output of %a %b %d gives you the date format displayed as you want. but since you have used the same alias as the column name, when you go to sort it is doing exactly thatbut you have transformed 2007-07-31 into 'Tue Jul 31' so that will sort after 'Fri Aug 1' since 'T' comes after 'F' in the alphabet. using a different alias for your formatted date means that the original format of your game_date column is sorted on.