MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:riga.riga Date:July 5 2002 2:57pm
Subject:Re: join with tabla.col=max(tablea.col)
View as plain text  
Thanks for your reply, but my problem is not solved by your suggestions:

> mysql>> select *,max(cb) from testb left join testa using (ca) group by
> > testb.ca;
> This  query selects all the fields from testb (ca, time) AND
> testa (ca, cb) because of the LEFT JOIN you used. You should
> try specifying the just fields you need:

OK, you are right, but thats not what i meant:


> mysql>> select *,max(cb) as mm from testb left join testa using (ca) where
> > testa.cb=mm group by testb.ca;
> > ERROR 1054: Unknown column 'mm' in 'where clause'
> mysql>> select *,max(cb) as mm from testb left join testa using (ca) where
> > testa.cb=max(cb) group by testb.ca;
> > ERROR 1111: Invalid use of group function
> 
> You should be using the HAVING clause, linke this:
> 
> mysql> select testb.ca, testb.time, max(cb) from testb left join testa
> using (ca
> ) group by testa.ca having testb.ca=max(cb);
> +------+----------------+---------+
> | ca   | time           | max(cb) |
> +------+----------------+---------+
> |    3 | 20020705155534 |       3 |
> +------+----------------+---------+

What I need to get is 

+------+----------------+------+------+------+
| ca   | time           | ca   | cb   | mm   |
+------+----------------+------+------+------+
|    2 | 20020705145347 |    2 |    9 |    9 |
|    3 | 20020705145349 |    3 |    3 |    3 |
+------+----------------+------+------+------+

so i want to get the records of testa, that are also listed in testb
(ca-column), and have the maximum cb-value out of all records with the same
ca-value.
and it's not the max()-value that helps me instead of the cb-value; My
tables have much more columns filled with data and i have to get mysql to use the
record with the max()-value when grouping the records so i get the right
other data-columns of testa, too.

i want to get following:
ca cb
2 9
3 3
out of testa to be the result.

i guess i have to use a temporary table... :(

Richard

-- 
GMX - Die Kommunikationsplattform im Internet.
http://www.gmx.net

Thread
join with tabla.col=max(tablea.col)riga.riga5 Jul
  • Re: join with tabla.col=max(tablea.col)Aleksandar Bradaric5 Jul
    • Re: join with tabla.col=max(tablea.col)riga.riga5 Jul