Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    4

    Red face Unanswered: ORA-00937: not a single-group group function

    I am basically trying to retrieve a particular row from the database.

    I am using the following code inside a PL/SQL block

    select id,name into id_temp, name_temp from idtable where id in (select max(id) from idtable);

    When I run my PL/SQL it gives me the following error in this line number.

    ORA-00937: not a single-group group function

    I try to do it in SQL

    select id,name from idtable where id in ( select max(id) from idtable);

    it works. So does the error has something to do with the usage of INTO?

    Can you tell me where I am going wrong? Excuse me if it is a very simple question as I am pretty new to SQL

    Thanks
    Last edited by watsup; 02-12-04 at 17:01.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: ORA-00937: not a single-group group function

    I'm unable to replicate your problem (using EMP table):
    PHP Code:
      1  declare
      
    2    v_empno emp.empno%type;
      
    3    v_ename emp.ename%type;
      
    4  begin
      5    select empno
    ename
      6    into v_empno
    v_ename
      7    from emp
      8    where empno in 
    (select max(empnofrom emp);
      
    9    dbms_output.put_linev_empno || ' : ' || v_ename );
     
    10end;
    SQL> /
    7934 MILLER 
    What does your PL/SQL look like?

  3. #3
    Join Date
    Jan 2004
    Location
    India
    Posts
    191

    Re: ORA-00937: not a single-group group function

    The query

    select id,name from idtable where id in ( select max(id) from idtable);

    will also work in plsql as you are not using any aggregate funtion in the main query.

    ORA-00937: not a single-group group function
    This error will only come when you use aggregate function plus
    other columns in the main query without the group by clause.

    Please give full plsql code
    Pagnint
    (No need to search web before posting new question)

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    why do you need an IN clause when you are selecting the MAX id?

    Anyways, I assume you are selecting into variables?

    As suggested, please post all of the code otherwise we are guessing.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Feb 2004
    Posts
    4

    Post Re: ORA-00937: not a single-group group function

    Thanks to everyone who replied my question.

    I was wrong. Only after reading your replies I noticed that I was using max function in the select statement twice.

    I was trying to do the following unlike wat i wrote in here and unlike wat I thought I was doing in the PL.SQL. It just went un-noticed

    select max(id),name into id_temp, name_temp from idtable where id in (select max(id) from idtable);

    sorry about that. sorry for wasting your time due my careless mistake..I am really sorry

    Thanks for the help.

    ************************************************** ******
    Originally posted by watsup
    I am basically trying to retrieve a particular row from the database.

    I am using the following code inside a PL/SQL block

    select id,name into id_temp, name_temp from idtable where id in (select max(id) from idtable);

    When I run my PL/SQL it gives me the following error in this line number.

    ORA-00937: not a single-group group function

    I try to do it in SQL

    select id,name from idtable where id in ( select max(id) from idtable);

    it works. So does the error has something to do with the usage of INTO?

    Can you tell me where I am going wrong? Excuse me if it is a very simple question as I am pretty new to SQL

    Thanks
    ************************************************** **********

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •