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]
