Selecting distinct values from mysql with largest timestamp
- by user987048
I am building a mail system. The inbox is only supposed to grab the last message (one with the highest time value) of a concatenation of user and sender, where the user or sender is the user ID.
Here is the table structure:
CREATE TABLE IF NOT EXISTS `mail` (
`user` int(11) NOT NULL,
`sender` int(11) NOT NULL,
`body` text NOT NULL,
`new` enum('0','1') NOT NULL default '1',
`time` int(11) NOT NULL,
KEY `user` (`user`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
So, with a table with the following data:
user sender new time
*****************************************
1 0 0 5
1 0 0 6
2 1 0 7
1 0 1 8
1 2 0 9
1 0 1 11
1 2 1 12
I want to select the following:
WHERE USER OR SENDER = X (in this case, 1)
user sender new time
*****************************************
2 1 0 7
1 2 0 9
1 0 1 11
How would I go about doing something like this?
