That does exactly what I needed. Thanks much for the tip, it'll save me a weekend digging through the web trying to finish a project for work.
Alec On Sat, 26 Jul 2003, Yves Goergen wrote: > 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]
