I have a table with the following format. (Sorry for the bad formatting of the table, not sure how to do that well here.)

message_schedule_id 	sub_id 	message_type_id 	message_id 	message_schedule_date
 	1 	1 	1 	1 	2011-01-01
 	2 	1 	2 	2 	2011-01-01
 	3 	1 	3 	3 	2011-01-01
 	4 	1 	4 	4 	2011-01-01
	5 	1 	1 	5 	2011-02-02
 	6 	1 	3 	6 	2011-02-02
	7 	1 	4 	7 	2011-01-17
 	8 	1 	4 	8 	2011-02-03
 	9 	1 	4 	9 	2011-02-14

What I am attempting to do is select the most recent (not in the future) scheduled message (message_id) from each message_type_id so the goal is to select message_id 9, 6, 2 and 5. I'm assuming I can do this with one query but I'm fairly inexperienced with anything more than basic joins and this one is giving me trouble. How can I do this?

    You select the ids by joining on a nested query (same table) aliased to some name (b in this case), where the inne rquery retrieves max date for each type_id. The join is performed on type_id and date.

    SELECT a.message_schedule_id
FROM t
	INNER JOIN (
		SELECT MAX(message_schedule_date) AS d, type_id
		FROM t
		WHERE message_schedule_date <= CURDATE()
		GROUP BY type_id
	) b ON b.type_id = a.type_id AND b.message_schedule_date = a.message_schedule_date
      Write a Reply...