I have two tables as below:
CREATE TABLE domain_types (
type_id INT(4) NOT NULL AUTO_INCREMENT,
name VARCHAR(10) UNIQUE NOT NULL,
description VARCHAR(75),
PRIMARY KEY(type_id)
) TYPE=INNODB COMMENT="Types of domains we store";
CREATE TABLE domains (
domain_id INT(6) NOT NULL AUTO_INCREMENT,
domain VARCHAR(50) UNIQUE NOT NULL,
type_id INT(4) NOT NULL,
PRIMARY KEY(domain_id)
) TYPE=INNODB COMMENT="Domains";
I can get the below result easily:
mysql> select distinct t.name,d.domain from domain_types t, domains d
WHERE t.type_id=d.type_id ORDER BY t.type_id;
+----------+------------+
| name | domain |
+----------+------------+
| hostdom1 | abc123.com |
| hostdom1 | abc124.com |
| hostdom1 | abc125.com |
| hostdom2 | abc127.com |
| hostdom2 | abc126.com |
| hostdom3 | abc128.com |
| hostdom4 | abc129.com |
| hostdom4 | abc130.com |
+----------+------------+
8 rows in set (0.01 sec)
But what I really need is a result like
+----------+------------+
| name | domain |
+----------+------------+
| hostdom1 | abc123.com |
| hostdom2 | abc127.com |
| hostdom3 | abc128.com |
| hostdom4 | abc129.com |
+----------+------------+
where only the t.name and d.domain pair with the highest
domain_id for each type_id are given. Is there a way to do this without
resulting to seperate SQL queries for each entry in the domain_types
table?
Alec
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/my***********...ie.nctu.edu.tw