List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:May 12 2002 11:05pm
Subject:Re: substitute for sub query with group function
View as plain text  
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

Thread
substitute for sub query with group functionSabine Richter12 May
  • Re: substitute for sub query with group functionPhilip Spradling12 May
  • Re: substitute for sub query with group functionSabine Richter12 May
    • Re: substitute for sub query with group functionPhilip Spradling12 May
    • Re: substitute for sub query with group functionPaul DuBois13 May