-
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
-
I haven't been able to reproduce it!
Try "order by 1 desc".
-
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
-
Try this:
select * from
( select /*+ no_merge */
distinct invoicenumber
from invoices i
where hnum='2222222' )
order by invoicenumber desc ;
Tamil
-
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?
-
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
-
I think your time has come to uprgade to atleast 8.1.7.4.
Tamil
-
yes tamilselvan. it is time to upgrade.
till then you will be troubled friends!!!!!
thank you everybody.
-Raja
-
Try ...
Code:
select*
from
(
select distinct
invoicenumber
from
invoices i
where
hnum='2222222' and
rownum > 1 )
order by
invoicenumber desc
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|