Difficult query

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

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?

On 19 apr, 12:01, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On Apr 19, 10:52 am, eebieee...@hotmail.com wrote:
>
>
> 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?

You are right, I said it wrong. I only want the values where column2
is maximum , most of the time that gives one value but not all the
time.

--
EeBie

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

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