Selecting unique values:K@N@!:

[Alec Smith]

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? I'm using MySQL 4.0.14 on FreeBSD 5.1.

Alec

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/my******************@paypal.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/my***********...ie.nctu.edu.tw