On Apr 19, 11:01 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On Apr 19, 10:52 am, eebieee...@hotmail.com wrote:
>
>
>
> > Hello,
>
> > First, excuses for my bad english.
>
> > I have a table:
>
> > mysql> select * from test;
> > +----+---------+---------+------+
> > | id | column1 | column2 | text |
> > +----+---------+---------+------+
> > | 1 | 1 | 1 | qwe |
> > | 2 | 2 | 1 | asd |
> > | 3 | 3 | 1 | zxc |
> > | 4 | 4 | 1 | zaq |
> > | 5 | 1 | 2 | xsw |
> > | 6 | 2 | 2 | cde |
> > | 7 | 3 | 2 | ewq |
> > | 8 | 4 | 2 | dsa |
> > | 9 | 1 | 3 | cxz |
> > | 10 | 2 | 3 | esz |
> > | 11 | 3 | 3 | csq |
> > | 12 | 4 | 2 | zse |
> > +----+---------+---------+------+
>
> > I want to select all the values from column1 one time and the maximum
> > value of column2 that correspond with that and also the value of the
> > column text that is on the same row.
>
> > I have the next solution but it isn't very nice:
>
> > mysql> CREATE TABLE tmp SELECT column1, MAX(column2) AS column2 FROM
> > test GROUP BY column1;
>
> > mysql> select test.column1, test.column2, text FROM test, tmp WHERE
> > test.column1 = tmp.column1 AND test.column2 = tmp.column2;
> > +---------+---------+------+
> > | column1 | column2 | text |
> > +---------+---------+------+
> > | 1 | 3 | cxz |
> > | 2 | 3 | esz |
> > | 3 | 3 | csq |
> > | 4 | 2 | dsa |
> > | 4 | 2 | zse |
> > +---------+---------+------+
>
> > mysql> DROP table tmp;
>
> > Does anybody know a nicer way to that? I tried joins and a lot of
> > other things but this is the only way I got it working.
>
> > Again, excuses for my bad engisch (I'm from holland)
>
> > --
> > EeBie
>
> Your requirement says: "I want to select all the values from column1
> one time", but your sample output has two entries for the column1
> value "4". These contradict each other. Which do you actually want to
> see?

I'm assuming EeBie wants to see all results holding the max value for
each group:

SELECT t1. *
FROM test t1
LEFT JOIN test t2 ON t2.column1 = t1.column1
AND t1.column2 < t2.column2
WHERE t2.column2 IS NULL


BTW EeBie, don't apologise. Your English is almost perfect:

correspond -> corresponds
next -> following