Select query on timestamp

[safra]

Hi,

I have a mailing system with the option to send mail to all members at once. They will all have the same timestamp. In my Sent Items box I only want to display emails that were send to all members once with something like "send to all members" in the recipient column.

How would I query MySQL to only return 1 record when records have the same timestamp?

Thanks,
Raoul

[johanafm]

It depends on your schema. If you have something along the lines of

CREATE TABLE message (
	id	INT	UNSIGNED AUTO_INCREMENT,
	body	TEXT,
PRIMARY KEY(id));

CREATE TABLE sent (
	time		INT UNSIGNED,
	from	fid	INT UNSIGNED,
	to	tid	INT UNSIGNED,
	msg		INT UNSIGNED
PRIMARY KEY(time, msg, to),
FOREIGN KEY(msg) REFERENCES message(id));

Then just

SELECT body FROM message WHERE id IN (SELECT msg FROM sent WHERE time=timestamp AND from=someone)

If you actually store the message one time per recipient, then GROUP BY message.id, or LIMIT 0,1. If the only thing you use in your where clause is the timestamp, then there could in reality be two or more different messages sent at that exact time. GROUP BY would return all of them, while limit might very well get you a message you didn't want in that case.

[safra]

Thanks johanafm, GROUP BY did it!