On 19 apr, 12:29, strawberry <zac.ca...@gmail.com> wrote:
> 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

That works! I don't know exactly how but I try to figure it out.
Thank you.

> BTW EeBie, don't apologise. Your English is almost perfect:
>
> correspond -> corresponds
> next -> following

:-)

--
EeBie