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/[EMAIL PROTECTED]
