Hi,
Can I ask MySQL to GROUP BY id on MAX(something). For example, the query
below:
mysql> SELECT diary.date, diary.time, diary.id AS diary_id, diary.title,
-> link.to_id AS friend_id
-> FROM link
-> JOIN diary ON diary.member_id = link.to_id
-> WHERE link.from_id=10
-> ORDER BY diary.id DESC
-> LIMIT 5
-> ;
+------------+----------+----------+---------+-----------+
| date | time | diary_id | title | friend_id |
+------------+----------+----------+---------+-----------+
| 2004-04-02 | 18:27:49 | 57 | tes | 22 |
| 2004-02-23 | 13:55:30 | 20 | fdas | 6 |
| 2004-02-23 | 13:55:16 | 19 | dfasfsd | 6 |
| 2004-02-23 | 13:55:03 | 18 | fasd | 6 |
| 2004-02-23 | 13:54:55 | 17 | daf | 6 |
+------------+----------+----------+---------+-----------+
5 rows in set (0.01 sec)
What I want is:
+------------+----------+----------+---------+-----------+
| date | time | diary_id | title | friend_id |
+------------+----------+----------+---------+-----------+
| 2004-04-02 | 18:27:49 | 57 | tes | 22 |
| 2004-02-23 | 13:55:30 | 20 | fdas | 6 |
+------------+----------+----------+---------+-----------+
I only need the row with the MAX(diary_id) from the same friend_id. Here
is what I tried, but failed :(
mysql> SELECT diary.date, diary.time, diary.id AS diary_id, diary.title,
-> link.to_id AS friend_id
-> FROM link
-> JOIN diary ON diary.member_id = link.to_id
-> WHERE link.from_id=10
-> GROUP BY link.to_id
-> ORDER BY diary.id DESC
-> LIMIT 5
-> ;
+------------+----------+----------+-------+-----------+
| date | time | diary_id | title | friend_id |
+------------+----------+----------+-------+-----------+
| 2004-04-02 | 18:27:49 | 57 | tes | 22 |
| 2004-02-21 | 11:08:57 | 4 | test | 6 |
+------------+----------+----------+-------+-----------+
2 rows in set (0.00 sec)
The diary_id for friend_id 6 is 4. I want it to be 20 which is the
biggest diary_id value for friend_id 6. How do I do this? Thank you very
much.
Regards,
--Batara
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
