Home » SQL & PL/SQL » SQL & PL/SQL » using self join to get such order,why? (ORACLE 10g Windows)
using self join to get such order,why? [message #441923] Wed, 03 February 2010 23:21 Go to next message
slightleaves
Messages: 15
Registered: August 2007
Location: china
Junior Member

I want to execute a query to get some special results
create table A_TEST4
(
  PID VARCHAR2(20) not null,
  NUM NUMBER(10)
)

insert into A_TEST4 (PID, NUM)
values ('a', 2);
insert into A_TEST4 (PID, NUM)
values ('c', 3);
insert into A_TEST4 (PID, NUM)
values ('d', 7);
insert into A_TEST4 (PID, NUM)
values ('e', 1);
insert into A_TEST4 (PID, NUM)
values ('f', 5);
insert into A_TEST4 (PID, NUM)
values ('g', 4);
insert into A_TEST4 (PID, NUM)
values ('h', 8);
commit;
select * from a_test4 t;
 
PID                          NUM
-------------------- -----------
a                              2
c                              3
d                              7
e                              1
f                              5
g                              4
h                              8
 

,after I execute the sql below ,I found the results had been already ordered by num, why it can get such order?
 SELECT T1.NUM, COUNT(T1.NUM) N
     FROM A_TEST4 T1, A_TEST4 T2
    WHERE T1.NUM >= T2.NUM
    GROUP BY T1.NUM;
 
        NUM          N
----------- ----------
          8          7
          7          6
          5          5
          4          4
          3          3
          2          2
          1          1

Re: using self join to get such order,why? [message #441927 is a reply to message #441923] Wed, 03 February 2010 23:27 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
>,after I execute the sql below ,I found the results had been already ordered by num, why it can get such order?

Forgive me, but I am not sure exactly what you want or what the problem is.
Re: using self join to get such order,why? [message #441932 is a reply to message #441923] Wed, 03 February 2010 23:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I found the results had been already ordered by num, why it can get such order?

You can get ANY order.

Regards
Michel
Re: using self join to get such order,why? [message #441935 is a reply to message #441927] Wed, 03 February 2010 23:42 Go to previous messageGo to next message
slightleaves
Messages: 15
Registered: August 2007
Location: china
Junior Member

BlackSwan wrote on Wed, 03 February 2010 23:27
>,after I execute the sql below ,I found the results had been already ordered by num, why it can get such order?

Forgive me, but I am not sure exactly what you want or what the problem is.



Actually,I want to get the middle num of the column 'num', I use the sql
 SELECT *
    FROM (SELECT T1.NUM, COUNT(T1.NUM) N
             FROM A_TEST4 T1, A_TEST4 T2
            WHERE T1.NUM >= T2.NUM
            GROUP BY T1.NUM)
   WHERE N = (SELECT ROUND(COUNT(1) / 2) FROM A_TEST4);
 
        NUM          N
----------- ----------
          4          4



,so wonder why I can get ordered results by the sql below
SELECT T1.NUM, COUNT(T1.NUM) N
    FROM A_TEST4 T1, A_TEST4 T2
   WHERE T1.NUM >= T2.NUM
   GROUP BY T1.NUM;
 
        NUM          N
----------- ----------
          8          7
          7          6
          5          5
          4          4
          3          3
          2          2
          1          1
 
7 rows selected
Re: using self join to get such order,why? [message #441936 is a reply to message #441935] Wed, 03 February 2010 23:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The ONLY way to get an order it to use ORDER BY.

Regards
Michel
Re: using self join to get such order,why? [message #441937 is a reply to message #441935] Wed, 03 February 2010 23:46 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
>so wonder why I can get ordered results by the sql below
The order of returned rows is indeterminate & can change.
Just accept the results or use ORDER BY if you need/want a specific return order.
Re: using self join to get such order,why? [message #441938 is a reply to message #441937] Wed, 03 February 2010 23:48 Go to previous messageGo to next message
slightleaves
Messages: 15
Registered: August 2007
Location: china
Junior Member

BlackSwan wrote on Wed, 03 February 2010 23:46
>so wonder why I can get ordered results by the sql below
The order of returned rows is indeterminate & can change.
Just accept the results or use ORDER BY if you need/want a specific return order.


I mean,, I don't use order by clause,but the results have been ordered by num, why?
Re: using self join to get such order,why? [message #441939 is a reply to message #441938] Wed, 03 February 2010 23:51 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
> I don't use order by clause,but the results have been ordered by num, why?

The order of returned rows is indeterminate & can change.
Just accept the results or use ORDER BY if you need/want a specific return order.
Re: using self join to get such order,why? [message #441951 is a reply to message #441938] Thu, 04 February 2010 00:47 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
slightleaves wrote on Thu, 04 February 2010 06:48
BlackSwan wrote on Wed, 03 February 2010 23:46
>so wonder why I can get ordered results by the sql below
The order of returned rows is indeterminate & can change.
Just accept the results or use ORDER BY if you need/want a specific return order.


I mean,, I don't use order by clause,but the results have been ordered by num, why?

The results were NOT ordered by num. They came out in a more or less random order, which happened to be what you saw.
If you want a resultset to be sorted, you have to use order by. Now, what is the problem with adding three words to your query?!
Re: using self join to get such order,why? [message #441952 is a reply to message #441951] Thu, 04 February 2010 00:54 Go to previous message
slightleaves
Messages: 15
Registered: August 2007
Location: china
Junior Member

thanks for all above,
and I understand the that results was HAPPENED TO BE what I saw
Previous Topic: ORA-06502: Host bind array to small (web service callout)
Next Topic: Need to retrieve object elements
Goto Forum:
  


Current Time: Sat Dec 03 19:54:01 CST 2016

Total time taken to generate the page: 0.10781 seconds