DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Subselects

  1. #1
    Join Date
    Jan 2002
    Posts
    152
    I've got this select with subselects,and I want to get the first 5 values.That's OK.But when I try to do the same in one of the subselects I always get ORA-00907 with the order by...

    select * from(select provincias.descripcion as Provincia,count(provincia) as numero,
    (Select count(provincia) from empresas where provincia not in
    (select(Select provincia from empresas group by provincia order by count(provincia) desc)where rownum <=5) as resto
    from empresas,provincias where empresas.provincia=provincias.codigo
    group by provincia,descripcion order by count(provincia) desc)
    where rownum <=5;

    Can somebody help me to do it right? Thanks

  2. #2
    Join Date
    May 2002
    Posts
    108
    subselects I always get ORA-00907

    Jamoji,

    There is an extra select( in your query (ie., the fourth SELECT). Removing this alone will not solve the problem

    Oracle particularly doesn't like an Order by clause within the subquery, if the subquery forms a COLUMN of a query.

    Even a simple example like this fails :
    select sal, (select max(sal) sal1 from emp order by sal1) from emp


    whereas this works :
    select sal, (select max(sal) sal1 from emp) from emp

    You have to remove the Order by clause from with in the subselect. It will not give you the result you intend.

    You got to find a different work around.

    Honestly do you think this is the only way to solve your problem?

    May be I don't understand your problem ! If I do, I believe you can pull them out as different queries. Then use set operator UNION, and group data accordingly as required. I have suggested it earlier as well.

    Cheers,
    Nandu

    Never give up !

    Nanda Kumar - Vellore

  3. #3
    Join Date
    Jan 2002
    Posts
    152
    Thanks Nandu.
    The problem is I'm translating procedures from other people, and from SQL Server to migrate the DataBase to Oracle,and I don't like to change things,cause I don't understand very well their way of working...you know.
    Thanks anyway

  4. #4
    Join Date
    Jan 2002
    Posts
    152
    The question is to select first 5 rows from provincias.descripcion as Provincia,count(provincia) as numero
    and then,the 3rd field is "resto" and contains Select count(provincia) from empresas where provincia not in "the first 5 rows from a subselect)...
    That's the problem,when I try to add this 3rd row.If I show only provincias.descripcion as Provincia,count(provincia) as numero,there's no problem.
    So I think it's something related to the subselect...

  5. #5
    Join Date
    May 2002
    Posts
    108
    Jamoji,

    In short, Can I say you require Provincia, count(top five provincia), count(next five provincia)?

    ie., if provincia are A,B,C,D.... then

    Col1 Col2 Col3
    ---- ---- ----
    A 15 10
    B 14 9
    C 13 8
    D 12 7
    E 11 6

    Is this what you requrie?


    Cheers
    Nandu

    Never give up !

    Nanda Kumar - Vellore

  6. #6
    Join Date
    Jan 2002
    Posts
    152
    Yes,more or less...
    count(next five provincia) will be:
    select count(provincia) from table where provincia not in
    (select first five provincia...)

  7. #7
    Join Date
    May 2002
    Posts
    108

    Does it mean the provincia desc for the next five provincia has no significance and you require only the count?

    Even if your query works, the output will be first five provincia desc as col1, the COUNT of them as col2 and merely the COUNT of next five provincia as col3.

    Is it true? Or you would require the provincia desc of the next five column as well?

    Can you print here a sample of your output?

    Nandu
    Never give up !

    Nanda Kumar - Vellore

  8. #8
    Join Date
    Jan 2002
    Posts
    152
    My required output is:

    Provincia numero resto
    ------------------------- ----------- -----------
    SALAMANCA 93 4
    MADRID 27 4
    BARCELONA 19 4
    ÁLAVA 4 4
    ALICANTE 4 4

    The "resto" column is only the same value,just for the programmers...but it depends on the content in the select with the input parameters you give....

  9. #9
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    hi jamoji,

    is this what u want?

    select * from (select provincias.descripcion as Provincia, count(provincia) as numero, (Select count(provincia) from empresas where provincia not in (select aa from (Select provincia aa, count(provincia) bb from empresas group by provincia order by count(provincia) desc) where rownum <=5) ) as resto from empresas,provincias where empresas.provincia=provincias.codigo group by provincia,descripcion order by count(provincia) desc
    ) where rownum <=5;

    Cheers!
    OraKid.

  10. #10
    Join Date
    Jan 2002
    Posts
    152
    No,it doesn´t work.
    In my select ,I return 3 fields:
    1-first one must be first 5 registers from descripcion as provincia
    2-count(provincia) as numero
    3-(Select count(provincia) from empresas where provincia not in
    (select * from(Select provincia from empresas group by provincia order by count(provincia) desc)where rownum <=5)) as resto

    the 3rd one must be this way,because I have tried it,and that's what I nedd it to return.
    The problem is,I'm translating it from SQL Server,and there must be something wrong related to so many groups by,order by or something...
    Here is the original select in SQL Server:

    select top 5 descripcion as Provincia,count(provincia) as numero,
    (Select count(provincia) from empresas where provincia not in
    (Select top 5 provincia from empresas group by provincia order by count(provincia) desc)) as resto
    from empresas inner join provincias on empresas.provincia=provincias.codigo
    group by provincia,descripcion order by count(provincia) desc;

    If I try it without the 3rd field,it works.And if I try the selects of the 3rd field alone,it works too.The problem is when I join them....

Posting Permissions

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


Click Here to Expand Forum to Full Width