At 23:41 +0200 5/12/02, Sabine Richter wrote:
>Hello Philip,
>
>that was my first choice, too.
>But no, error 1111: Invalid use of group function
>
>and select col1, max(col2) from table;
>is error 1140: mixing of group cols with non groupü cols is illegal
>(That would other sql backends say too)
And they should.
>
>and other variants of the above like "select col1 from table t1, table
>t2 where ....=max(col2)" result in the same error reports
>
>Perhaps someone else has an idea?
If you don't mind running two queries, do this:
SELECT @max := MAX(col2) FROM t2;
SELECT col1 FROM t1 WHERE col2 = @max;
>
>Best regards
>Sabine
>
>
>Philip Spradling wrote:
>>
>> I could be missing something, but what about:
>>
>> select col1 from t1 where col2 = max(col2);
>>
>> On Sun, 12 May 2002 23:04:14 +0200
>> Sabine Richter <sabine@stripped> wrote:
>>
>> > Hello,
>> >
>> > I just want to get the value of one column in the row where max of
>> > another col of this table is.
>> > With the possibility of sub queries I would write:
>> >
>> > select col1
>> > from table t1
>> > where col2 = (select max(col2)
>> > from tab1e t2);
>> >
>> >
>> > But how to do it without a sub query?
>> > Just with 1 statement?
>> > I find no solution.
>> >
>> > Not wanting to believe there is none
> > > Sabine
