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

Thread: order by desc with distinct

  1. #1
    Join Date
    Jan 2003
    Location
    india
    Posts
    175

    order by desc with distinct

    hi,
    i have a problem in listing the rows order by desc.

    if i do not use distinct then no problem.

    if i use "distinct" and "order by desc" together
    it gets sorted as ascending not descending.


    this is the table structure

    INVOICENUMBER NUMBER(7)
    HNUM VARCHAR2(7)


    here is the screen shot.

    1 select invoicenumber
    2 from invoices i
    3 where hnum='2222222'
    4 order by invoicenumber desc ;
    SQL>

    INVOICENUMBER
    -------------
    35317
    35119
    35117

    1 select distinct invoicenumber
    2 from invoices i
    3 where hnum='2222222'
    4 order by invoicenumber desc ;
    SQL>

    INVOICENUMBER
    -------------
    35117
    35119
    35317
    SQL>

    any idea of why and how to overcome this problem.


    with thanks,

    Raja

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    I haven't been able to reproduce it!

    Try "order by 1 desc".

  3. #3
    Join Date
    Jan 2003
    Location
    india
    Posts
    175
    i am sorry not to have mentioned i use
    oracle 8.1.5 on win NT.

    "order by 1 desc" also did not get expected result.

    anything else?

    thank you!

    -Raja

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Try this:

    select * from
    ( select /*+ no_merge */
    distinct invoicenumber
    from invoices i
    where hnum='2222222' )
    order by invoicenumber desc ;

    Tamil

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Raja, are you giving us a guided tour of the bugs in 8.1.5 ?

    Metalink shows some some bugs similar to yours, but not the same. It's anyone's guess . . .

    Time to upgrade?

  6. #6
    Join Date
    Jan 2003
    Location
    india
    Posts
    175
    hi tamilselvan,
    this did not solve my problem.

    any other method?

    thank you so much!


    SQL> select * from
    2 ( select /*+ no_merge */
    3 distinct invoicenumber
    4 from invoices i
    5 where hnum='2222222' )
    6 order by invoicenumber desc ;
    select * from
    *
    ERROR at line 1:
    ORA-03113: end-of-file on communication channel

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    I think your time has come to uprgade to atleast 8.1.7.4.

    Tamil

  8. #8
    Join Date
    Jan 2003
    Location
    india
    Posts
    175
    yes tamilselvan. it is time to upgrade.

    till then you will be troubled friends!!!!!

    thank you everybody.

    -Raja

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Try ...

    Code:
    select*
    from
       (
       select distinct
          invoicenumber
       from
          invoices i
       where
          hnum='2222222' and
          rownum > 1 )
    order by
       invoicenumber desc
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  10. #10
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by slimdave
    Try ...

    Code:
    ... and
          rownum > 1 )
    ...
    That should probably read:
    Code:
    ... and
          rownum >= 1 )
    ...
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

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