CODEWITHSUNDEEP
mysqlsqljoinsyntax
Thufir
Thufir

Reputation: 8497

query max on a join

How do I get the max MESSAGENUMBER?

mysql> 
mysql> describe ARTICLE;describeNEWSGROUP;
+---------------+------------+------+-----+---------+----------------+
| Field         | Type       | Null | Key | Default | Extra          |
+---------------+------------+------+-----+---------+----------------+
| ID            | bigint(20) | NO   | PRI | NULL    | auto_increment |
| MESSAGENUMBER | int(11)    | YES  |     | NULL    |                |
| NEWSGROUP_ID  | bigint(20) | YES  | MUL | NULL    |                |
+---------------+------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| ID        | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| NEWSGROUP | varchar(255) | YES  |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> 
mysql> select * from ARTICLE left join NEWSGROUP on ARTICLE.NEWSGROUP_ID=NEWSGROUP.ID where NEWSGROUP.newsgroup = "gwene.com.economist";
+----+---------------+--------------+------+---------------------+
| ID | MESSAGENUMBER | NEWSGROUP_ID | ID   | NEWSGROUP           |
+----+---------------+--------------+------+---------------------+
| 18 |             4 |            4 |    4 | gwene.com.economist |
| 19 |             5 |            4 |    4 | gwene.com.economist |
| 20 |             6 |            4 |    4 | gwene.com.economist |
| 21 |             7 |            4 |    4 | gwene.com.economist |
| 22 |             8 |            4 |    4 | gwene.com.economist |
| 23 |             9 |            4 |    4 | gwene.com.economist |
| 24 |            10 |            4 |    4 | gwene.com.economist |
| 25 |            11 |            4 |    4 | gwene.com.economist |
+----+---------------+--------------+------+---------------------+
8 rows in set (0.00 sec)

mysql>

Upvotes: 0

Views: 64

Answers (2)

XN16
XN16

Reputation: 5889

SELECT MAX(MESSAGENUMBER)
FROM ARTICLE left join NEWSGROUP on ARTICLE.NEWSGROUP_ID=NEWSGROUP.ID 
WHERE NEWSGROUP.newsgroup = "gwene.com.economist"

Upvotes: 1

Samson
Samson

Reputation: 2821

select * from ARTICLE left join NEWSGROUP 
on ARTICLE.NEWSGROUP_ID=NEWSGROUP.ID 
where NEWSGROUP.newsgroup = "gwene.com.economist" 
ORDER BY MESSAGENUMBER DESC LIMIT 1

Upvotes: 1

Related Questions