Home » SQL & PL/SQL » SQL & PL/SQL » How To Sort Data Without Using Order BY Clause
How To Sort Data Without Using Order BY Clause [message #283898] Wed, 28 November 2007 06:48 Go to next message
javed.khan
Messages: 340
Registered: November 2006
Location: Banglore
Senior Member

Dear Experts,


How To Sort Data Without Using Order BY Clause


Jak
Re: How To Sort Data Without Using Order BY Clause [message #283900 is a reply to message #283898] Wed, 28 November 2007 06:51 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

How To Sort Data Without Using Order BY Clause
But why you don't want to use ORDER BY clause.
without ORDER BY clause you can't sort your data.
icon9.gif  Re: How To Sort Data Without Using Order BY Clause [message #283901 is a reply to message #283898] Wed, 28 November 2007 06:52 Go to previous messageGo to next message
javed.khan
Messages: 340
Registered: November 2006
Location: Banglore
Senior Member

I just wanted to know is that possible to do so. My PL says find it out its possible.
Re: How To Sort Data Without Using Order BY Clause [message #283903 is a reply to message #283901] Wed, 28 November 2007 06:54 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

>My PL says find it out its possible.
I don't know who is PL and what is PL ?
>I just wanted to know is that possible to do so
It is not possible.
icon13.gif  Re: How To Sort Data Without Using Order BY Clause [message #283906 is a reply to message #283898] Wed, 28 November 2007 06:57 Go to previous messageGo to next message
javed.khan
Messages: 340
Registered: November 2006
Location: Banglore
Senior Member

PL-Project Leader.....

Shocked Impossible????
Are you Sure???
Re: How To Sort Data Without Using Order BY Clause [message #283907 is a reply to message #283906] Wed, 28 November 2007 06:58 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Ok, If think it is possible the please tell us (specially me) AFAIK it is not possible.
Re: How To Sort Data Without Using Order BY Clause [message #283909 is a reply to message #283901] Wed, 28 November 2007 07:01 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Just to re-enforce what Mohammad has said.

I'm assuming that PL is project leader (or maybe it's Party Liaison) either way if he/she is asking IF it is possible then the answer is categorically NO. If the are saying that it is possible then they are absolutely wrong.

It should be noted that this is a very commonly asked question and if you search, you will find that the threads always boil down to the same answer i.e. No.

[Updated on: Wed, 28 November 2007 07:01]

Report message to a moderator

Re: How To Sort Data Without Using Order BY Clause [message #283910 is a reply to message #283907] Wed, 28 November 2007 07:01 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Not possible. That's final. Perhaps your project leader is thinking of Index Organized Tables. But even then, without an order by clause your data is unsorted. That's a key feature of the relational database.

MHE
Re: How To Sort Data Without Using Order BY Clause [message #283912 is a reply to message #283898] Wed, 28 November 2007 07:02 Go to previous messageGo to next message
javed.khan
Messages: 340
Registered: November 2006
Location: Banglore
Senior Member

Sure vil let you know. Thanks for your Prompt replies.
Re: How To Sort Data Without Using Order BY Clause [message #283913 is a reply to message #283912] Wed, 28 November 2007 07:06 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

vil ?
You must read below link before any posting.[specially IM SPEAK section]
http://www.orafaq.com/forum/t/88153/0/
icon14.gif  Re: How To Sort Data Without Using Order BY Clause [message #283914 is a reply to message #283898] Wed, 28 November 2007 07:07 Go to previous messageGo to next message
javed.khan
Messages: 340
Registered: November 2006
Location: Banglore
Senior Member

Razz Thanks Guys...
Thanks alott. Now I will screw him(PL) Mad up.I said the same but i was not sure actually was flatuated by his prank.
Smile Thanks Guys
Re: How To Sort Data Without Using Order BY Clause [message #283916 is a reply to message #283914] Wed, 28 November 2007 07:12 Go to previous messageGo to next message
prashant.gupta
Messages: 7
Registered: November 2007
Location: INDIA
Junior Member
How about this !!!

select * from employee
UNION
select * from employee;

I think this will take only Distinct records as well as, Will provide the output in sorted order. Since using UNION gives the output in Sorted order.
Re: How To Sort Data Without Using Order BY Clause [message #283918 is a reply to message #283916] Wed, 28 November 2007 07:14 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Wrong. How many times does this have to be said?
THE ONLY WAY TO GUARANTEE AN ORDERED LIST IS TO USE THE ORDER BY CLAUSE (And yes I did mean to shout)

[Updated on: Wed, 28 November 2007 07:14]

Report message to a moderator

Re: How To Sort Data Without Using Order BY Clause [message #283920 is a reply to message #283918] Wed, 28 November 2007 07:16 Go to previous messageGo to next message
prashant.gupta
Messages: 7
Registered: November 2007
Location: INDIA
Junior Member
ok fine....

BUT

what will be the output of the above Query ????
Re: How To Sort Data Without Using Order BY Clause [message #283923 is a reply to message #283920] Wed, 28 November 2007 07:17 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
I would get a 942 error, why?
Re: How To Sort Data Without Using Order BY Clause [message #283926 is a reply to message #283923] Wed, 28 November 2007 07:21 Go to previous messageGo to next message
prashant.gupta
Messages: 7
Registered: November 2007
Location: INDIA
Junior Member
WHAT is 942 ????

Do u mean ORA- 942 error ?????
Re: How To Sort Data Without Using Order BY Clause [message #283928 is a reply to message #283926] Wed, 28 November 2007 07:22 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Well if you want to be picky, I actually meant ORA-00942.
Re: How To Sort Data Without Using Order BY Clause [message #283929 is a reply to message #283926] Wed, 28 November 2007 07:22 Go to previous messageGo to next message
prashant.gupta
Messages: 7
Registered: November 2007
Location: INDIA
Junior Member
Hi Mohammad Taj,

What do think the Query should work !!

Re: How To Sort Data Without Using Order BY Clause [message #283930 is a reply to message #283898] Wed, 28 November 2007 07:25 Go to previous messageGo to next message
javed.khan
Messages: 340
Registered: November 2006
Location: Banglore
Senior Member

Dear Mohammad,

I have also tried the same(with UNION) is returned me the Data in Sorted Order with first column.

Re: How To Sort Data Without Using Order BY Clause [message #283932 is a reply to message #283930] Wed, 28 November 2007 07:30 Go to previous messageGo to next message
prashant.gupta
Messages: 7
Registered: November 2007
Location: INDIA
Junior Member
If you would try this :

select empID, ename from employee
UNION
select empID, ename from employee;

This would provide the data in sorted order by 'empID' column.

UNION always sorts the query data, based on the 1st column.
Re: How To Sort Data Without Using Order BY Clause [message #283933 is a reply to message #283930] Wed, 28 November 2007 07:31 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

@OP and Prashant,

You want to know about EXCEPT ORDER BY CLASE we can sort data.
and the answer is NO NO NO.

and about UNION clause.
UNION clause use to know distinct value in both tables not for SORT purpose.
Re: How To Sort Data Without Using Order BY Clause [message #283934 is a reply to message #283930] Wed, 28 November 2007 07:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@javed.khan

To prove it is a rule you have to test it for all environments, all datatypes, all number of rows, all other parameters that you can imagine.
Can you do that?
No?
So your test is useless.

There is only ONE rule for order and it has already been posted.

Regards
Michel
Re: How To Sort Data Without Using Order BY Clause [message #283937 is a reply to message #283934] Wed, 28 November 2007 07:34 Go to previous messageGo to next message
prashant.gupta
Messages: 7
Registered: November 2007
Location: INDIA
Junior Member
Hi Mohammad,

You are right : UNION clause use to know distinct value in both tables not for SORT purpose.


BUT ultimately we are getting the result in Sorted order! and that's exactly the Question is ...
Re: How To Sort Data Without Using Order BY Clause [message #283940 is a reply to message #283937] Wed, 28 November 2007 07:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

BUT ultimately we are getting the result in Sorted order!

You have it for YOUR example.
I can build about infinite examples where you don't have it sorted.

Regards
Michel
Re: How To Sort Data Without Using Order BY Clause [message #283941 is a reply to message #283937] Wed, 28 November 2007 07:38 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

>that's exactly the Question is ...
No, that is not actual question. the question is "How To Sort Data Without Using Order BY Clause"

let try with your posted query
select empno, ename from emp
union
select empno,ename from emp;


I want sort rows in according column ename, can I get desire result without ORDER BY clause ?

Re: How To Sort Data Without Using Order BY Clause [message #283943 is a reply to message #283937] Wed, 28 November 2007 07:41 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Here is a demonstration which Michel gave justthe other day on an almost identical conversation
CREATE TABLE id10 (t varchar2(10));
INSERT INTO id10 VALUES('ťa');
INSERT INTO id10 VALUES('eb');

SELECT t FROM id10
union
SELECT t FROM id10
;
T          
---------- 
eb         
ťa         

2 rows selected

SELECT t FROM id10
union
SELECT t FROM id10
order by t

T          
---------- 
ťa         
eb         

2 rows selected

Which disproves your assertion. This should end the argument (I bet it doesn't tho').
Note, I altered the example slightly to create a table rather than use the with clause (I could just see it causing further confusions with this conversation)

[Updated on: Wed, 28 November 2007 07:43]

Report message to a moderator

Re: How To Sort Data Without Using Order BY Clause [message #283945 is a reply to message #283941] Wed, 28 November 2007 07:43 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Link

MHE
Re: How To Sort Data Without Using Order BY Clause [message #283949 is a reply to message #283941] Wed, 28 November 2007 07:50 Go to previous messageGo to next message
prashant.gupta
Messages: 7
Registered: November 2007
Location: INDIA
Junior Member
>I want sort rows in according column ename, can I get desire >result without ORDER BY clause ?

Well it may work !!

In such case you will have to modify the select clause :


select ename, empno from emp
union
select ename, empno from emp;

This should work.
Re: How To Sort Data Without Using Order BY Clause [message #283950 is a reply to message #283945] Wed, 28 November 2007 07:50 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Thanks Maarten,
Link now bookmarked and will now be my de-facto response to similar questions, it just saves so much hassle.
Re: How To Sort Data Without Using Order BY Clause [message #283958 is a reply to message #283943] Wed, 28 November 2007 08:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And to answer a question from yesterday (and today, now):
SQL> create table t (id integer, val integer, fil char(20))
  2  parallel 3
  3  partition by list (id)
  4  (
  5  partition p00 values (0),
  6  partition p01 values (1),
  7  partition p02 values (2),
  8  partition p03 values (3),
  9  partition p04 values (4),
 10  partition p05 values (5),
 11  partition p06 values (6),
 12  partition p07 values (7),
 13  partition p08 values (8),
 14  partition p09 values (9),
 15  partition p10 values (10),
 16  partition p11 values (11),
 17  partition p12 values (12),
 18  partition p13 values (13),
 19  partition p14 values (14),
 20  partition p15 values (15),
 21  partition p16 values (16),
 22  partition p17 values (17),
 23  partition p18 values (18),
 24  partition p19 values (19),
 25  partition p20 values (20),
 26  partition p21 values (21),
 27  partition p22 values (22),
 28  partition p23 values (23),
 29  partition p24 values (24),
 30  partition p25 values (25),
 31  partition p26 values (26),
 32  partition p27 values (27),
 33  partition p28 values (28),
 34  partition p29 values (29),
 35  partition p30 values (30),
 36  partition p31 values (31),
 37  partition p32 values (32),
 38  partition p33 values (33),
 39  partition p34 values (34),
 40  partition p35 values (35),
 41  partition p36 values (36),
 42  partition p37 values (37),
 43  partition p38 values (38),
 44  partition p39 values (39)
 45  )
 46  /

Table created.

SQL> insert into t (id, val) select mod(rownum,40), object_id from dba_objects where rownum <= 1000;

1000 rows created.

SQL> commit;

Commit complete.

SQL> select id from t where mod(id,3) in (0,2)
  2  union
  3  select id from t where mod(id,3) = 1
  4  /
        ID
----------
         1
         3
         4
         5
         6
        11
        12
        14
        15
        16
        17
        18
        21
        24
        28
        30
        31
        34
        35
        37
         0
         2
         7
         8
         9
        10
        13
        19
        20
        22
        23
        25
        26
        27
        29
        32
        33
        36
        38
        39

40 rows selected.

Regards
Michel
Re: How To Sort Data Without Using Order BY Clause [message #283961 is a reply to message #283958] Wed, 28 November 2007 08:09 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Nice demo ./fa/1581/0/
Re: How To Sort Data Without Using Order BY Clause [message #283965 is a reply to message #283961] Wed, 28 November 2007 08:18 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
That's what Mr. Kyte said too: partitioning, parallel operations, ... . There's no guaranteed order without an order by clause. And that's what Oracle will tell you too.

Great example Michel!

MHE
icon14.gif  Re: How To Sort Data Without Using Order BY Clause [message #284172 is a reply to message #283898] Thu, 29 November 2007 01:36 Go to previous message
javed.khan
Messages: 340
Registered: November 2006
Location: Banglore
Senior Member

Smile Thanks Alott michel, That is really a great example now the things are clear to me. Thanks Guys but I just want to thank Mr. Prashant also who enforce this topic to dig up to this level.
Thanks
Jak
Previous Topic: abt index
Next Topic: qry help plz ... (merged similar topics by same user)
Goto Forum:
  


Current Time: Sat Dec 03 10:17:33 CST 2016

Total time taken to generate the page: 0.08325 seconds