i'd try something like select t.name, d.domain from domain_types t, domains d where t.type_id = d.type_id group by t.name order by t.type_id
or something with a 'join on t.type_id = d.type_id' -yves -----Ursprüngliche Nachricht----- Von: "Alec Smith" <[EMAIL PROTECTED]> An: <[EMAIL PROTECTED]> Gesendet: Freitag, 25. Juli 2003 23:08 Betreff: Selecting unique values > 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/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
