1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

group function is not allower here?

Discussion in 'SQL PL/SQL' started by monkey, Jun 6, 2014.

  1. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    Hello!
    How to solve this problem then?

    Code (SQL):
    /*create table t1(
    tid int,
    tage int
    );

    insert into t1 values(1,10);
    insert into t1 values(1,20);
    insert into t1 values(1,50);
    insert into t1 values(1,30);*/


    SELECT tid AS id, tage AS age FROM t1 WHERE age=MAX(tage);
    Many thanks!!!
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    In a clause of WHERE it is impossible to use aggregation functions.

    For example :

    The approach used in the general case


    Code (SQL):


    SELECT tid  AS id ,tage  AS  age  FROM  t1 tage = (SELECT MAX(tage) FROM t1 );


     
    or
    use of analytics function :

    Code (SQL):


    SELECT * FROM (
    SELECT tid  AS id ,tage  AS  age ,MAX(tage) OVER () max_age  FROM  t1 )
    WHERE age =max_age;

     
     
    monkey likes this.
  3. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    O, thanks, that answers my another question!
    But, having does nto help too?

    Code (SQL):

    SELECT tid AS id, tage AS age FROM t1 HAVING tage= MAX(tage);
     
    Sorry, maybe looks a bit clumsi, but am still a beginner!
    ;Many thanks!!!
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    This query

    Code (SQL):
     
    SELECT tid  AS  id , tage  AS   age FROM t1 HAVING  tage = MAX (tage);
     
    won't work, since not set group by conditions
     
    monkey likes this.