Filtering join

[Eris Ristemena]

hi all,

i have this small problem. I hope someone can help me out here.

i have two table with one-to-many relations,
t1
id   name
1      a
2      b
3      c

t2
no   id   cust
1      1     x
2      1     y

using join statement like this:
select * from t1 left join t2 using (id)

i get this result:
id    name   no   id   cust
1        a       1     1     x
1        a       2     1     y
2        b      null  null  null
3        c      null  null  null

but what i need is a distinct t1.id with maximum t2.no, so that the result
should be like this:
id    name   no   id   cust
1        a       2     1     y
2        b      null  null  null
3        c      null  null  null

Can someone help me how? group by seem doesn't work.

regards,
-ers


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]