Alec Smith [EMAIL PROTECTED] wrote:
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.
Use SELECT ... GROUP BY name.
DISTINCT is applied to the all columns in the select list.
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Egor Egorov
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
