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

---

Quote:

---

Originally posted by slimdave
Try ...

Code:

---

... and
      rownum > 1 )
...

---

---

That should probably read:

Code:

---

... and
      rownum >= 1 )
...

---

Quote:

---

Originally posted by jmodic
That should probably read:

Code:

---

... and
      rownum >= 1 )
...

---

---

Damn you and your Slovenian sharp eyes. I suppose so.

Quote:

---

Originally posted by slimdave
Damn you and your Slovenian sharp eyes. I suppose so.

---

I was only returning you the ball - remember not so long ago you spotted a missing double-singe quotes inside the quoted string (hm, should I read/rewrite that agin immediately?) in one of my responses? :p

Quote:

---

Originally posted by jmodic
I was only returning you the ball - remember not so long ago you spotted a missing double-singe quotes inside the quoted string (hm, should I read/rewrite that agin immediately?) in one of my responses? :p

---

Oh yes, I remember that very well indeed.

Ah, happy days!

sorry, no luck.
what i got is...

1 select*
2 from
3 (
4 select distinct
5 invoicenumber
6 from
7 invoices i
8 where
9 hnum='2222222' and
10 rownum >= 1 )
11 order by
12* invoicenumber desc
SQL>
SQL> /
select*
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

What about avoiding explicit DISTINCT and using it implicitly? Something like:

Code:

---

select invoicenumber from invoices
 where hnum='2222222'
UNION
select invoicenumber from invoices
 where 1=2
order by invoicenumber desc;

---

oops! it's even worse.

SQL> select invoicenumber from invoices
2 where hnum='2222222'
3 UNION
4 select invoicenumber from invoices
5 where 1=2
6 order by invoicenumber desc;
select invoicenumber from invoices
*
ERROR at line 4:
ORA-00600: internal error code, arguments: [12451], [], [], [], [], [], [], []

SQL>

Well, that's it then -- time to get patching and upgrading. To my own recollection, 8.1.5 sucked.

everybody, thank you for the interest and effort.

-Raja