Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Location
    Antwerp - Belgium
    Posts
    2

    Unanswered: issue with 'inner select' and 'order by'

    Hi all, currently strugllig with below query (not working)
    SELECT *
    from
    (
    SELECT
    upper(ECOMM.REQUEST_LOG_SYS.COUNTRY),
    count(ECOMM.REQUEST_LOG_SYS.DOC_ID) aantal
    FROM
    ECOMM.REQUEST_LOG_SYS
    GROUP BY
    upper(ECOMM.REQUEST_LOG_SYS.COUNTRY)
    ORDER BY 2 DESC
    ) a
    where rownum < 11

    The idea of the 'a'-select is to kind of sort the values while the outside elect should return my TOP 10.

    Using Oracle 805 currently. realise the best would be an upgrade to 81x or higher and use a RANK but for now we are stuck with the 805.

    Any help is much appreciated.
    regards
    Ivo

  2. #2
    Join Date
    Jan 2004
    Location
    Hyderabad, India
    Posts
    37
    You cant use ORDER BY when you are using GROUP BY as GROUP BY does sorting by default on the GROUP BY column.

    So you can probably try the following query...

    SELECT *
    from
    (
    select country,aantal from (
    SELECT
    upper(ECOMM.REQUEST_LOG_SYS.COUNTRY) country,
    count(ECOMM.REQUEST_LOG_SYS.DOC_ID) aantal
    FROM
    ECOMM.REQUEST_LOG_SYS
    GROUP BY
    upper(ECOMM.REQUEST_LOG_SYS.COUNTRY)
    )
    order by 2
    )
    where rownum < 10

    I didnt test the query.. But i hope that works..

    where rownum < 11
    Regards
    Suneel

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by suneel.kumar
    You cant use ORDER BY when you are using GROUP BY as GROUP BY does sorting by default on the GROUP BY column.
    Yes you can - and in fact you should, because GROUP BY doesn't guarantee to return the results in any particular order (it usually does, but not always).

    In this case what is wanted is to order by a different set of columns than the GROUP BY uses. It works in 8.1.7.3.0:
    Code:
    SQL> select * from
      2  (
      3  select deptno, count(*) cnt
      4  from emp
      5  group by deptno
      6  order by 2 desc
      7  )
      8  where rownum < 3;
    
        DEPTNO        CNT
    ---------- ----------
            30          6
            20          5
    I guess that ORDER BY within an inline view wasn't available in 805. I can't think of any alternative at the moment, other than using PL/SQL like this:
    Code:
    DECLARE
      CURSOR c IS
        select deptno, count(*) cnt
        from emp
        group by deptno
        order by 2 desc;
    BEGIN
      FOR r IN c LOOP
        DBMS_OUTPUT.PUT_LINE(...);
        EXIT WHEN c%rowcount = 2;
      END LOOP;
    END;
    /

  4. #4
    Join Date
    Jan 2004
    Location
    Antwerp - Belgium
    Posts
    2
    Hi guys thks all. found a solution that works. Horrible SQL but tja 805 has its limitiations. Here goes


    SELECT
    upper(ECOMM.REQUEST_LOG_SYS.COUNTRY),
    count(ECOMM.REQUEST_LOG_SYS.DOC_ID)
    FROM
    ECOMM.REQUEST_LOG_SYS
    WHERE
    (
    ( upper(ECOMM.REQUEST_LOG_SYS.COUNTRY)
    in (
    SELECT
    upper(ECOMM.REQUEST_LOG_SYS.COUNTRY)
    FROM
    ECOMM.REQUEST_LOG_SYS
    GROUP BY upper(ECOMM.REQUEST_LOG_SYS.COUNTRY)
    having count(ECOMM.REQUEST_LOG_SYS.DOC_ID) >=
    (
    select min(nmbr)
    from
    (
    SELECT cntrymax , -nmbr nmbr
    from
    ( select -nmbr nmbr, cntry||to_char(nmbr) ,max(cntry) cntrymax
    from (
    SELECT
    upper(ECOMM.REQUEST_LOG_SYS.COUNTRY) cntry,
    count(ECOMM.REQUEST_LOG_SYS.DOC_ID) nmbr
    FROM
    ECOMM.REQUEST_LOG_SYS
    GROUP BY upper(ECOMM.REQUEST_LOG_SYS.COUNTRY)
    )
    group by -nmbr, cntry||to_char(nmbr))
    where rownum <=5
    )
    )
    ) )
    )
    GROUP BY
    upper(ECOMM.REQUEST_LOG_SYS.COUNTRY)

Posting Permissions

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