Home » SQL & PL/SQL » SQL & PL/SQL » order by in Minus Operator
order by in Minus Operator [message #283079] Mon, 26 November 2007 01:25 Go to next message
pragatimathur
Messages: 42
Registered: July 2006
Location: DELHI
Member
I am using order by with minus operator in my query but its giving error: "SQL Command Not Properly Ended"
Without Order By, output is coming but i have to use it
so please help me out.


SELECT
idp055.ROWID,
IDP010.AYBLNO
FROM
IDP055 ,
IDP010 ,
IDP050
WHERE
IDP055.EYBLNO = IDP010.AYBLNO AND
IDP010.AYBLNO = IDP050.BYBLNO
GROUP BY IDP055.ROWID,IDP010.AYBLNO
ORDER BY 1
MINUS
SELECT
idp055.ROWID,
IDP010.AYBLNO
FROM
IDP055 ,
IDP010 ,
IDP050
WHERE
IDP055.EYBLNO = IDP010.AYBLNO AND
IDP010.AYBLNO = IDP050.BYBLNO AND
ROWNUM<=3
GROUP BY IDP055.ROWID, IDP010.AYBLNO
ORDER BY 1
Re: order by in Minus Operator [message #283085 is a reply to message #283079] Mon, 26 November 2007 01:30 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Take out ORDER BY 1 common. Then it will work.

Regards,
Kiran
Re: order by in Minus Operator [message #283086 is a reply to message #283079] Mon, 26 November 2007 01:32 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

I mean to say try this query:

(SELECT 
idp055.ROWID,
IDP010.AYBLNO 
FROM 
IDP055 ,
IDP010 ,
IDP050
WHERE 
IDP055.EYBLNO = IDP010.AYBLNO AND
IDP010.AYBLNO = IDP050.BYBLNO
GROUP BY IDP055.ROWID,IDP010.AYBLNO) 
MINUS
(SELECT 
idp055.ROWID,
IDP010.AYBLNO 
FROM 
IDP055 ,
IDP010 ,
IDP050
WHERE 
IDP055.EYBLNO = IDP010.AYBLNO AND
IDP010.AYBLNO = IDP050.BYBLNO AND
ROWNUM<=3
GROUP BY IDP055.ROWID, IDP010.AYBLNO )
ORDER BY 1 


Kiran
Re: order by in Minus Operator [message #283087 is a reply to message #283079] Mon, 26 November 2007 01:33 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

I am not sure what you are trying to achieve
Anyway following hints may help you.

1. No need to use GROUP BY IDP055.ROWID,IDP010.AYBLNO if you want ot select IDP055.ROWID IDP010.AYBLNO . rather Use DISTINCT Keyword

2. Minus generally select the rows in the order it is selected.

Thumbs Up
Rajuvan.

[Updated on: Mon, 26 November 2007 01:35]

Report message to a moderator

Re: order by in Minus Operator [message #283089 is a reply to message #283079] Mon, 26 November 2007 01:39 Go to previous messageGo to next message
tanmoy1048
Messages: 133
Registered: June 2007
Senior Member
select * from (select ...... order by)
minus
select * from (select ....... order by)
Re: order by in Minus Operator [message #283090 is a reply to message #283079] Mon, 26 November 2007 01:41 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Quote:

Minus generally select the rows in the order it is selected.


Eg:

SQL> select rowid,ENAME from emp where DEPTNO=20;

ROWID              ENAME
------------------ ----------
AAAyd2AAHAAAAoeAAA SMITH
AAAyd2AAHAAAAoeAAD JONES
AAAyd2AAHAAAAoeAAH SCOTT
AAAyd2AAHAAAAoeAAK ADAMS
AAAyd2AAHAAAAoeAAM FORD


SQL> select rowid,ENAME from emp where DEPTNO=20
  2    minus
  3    select   rowid,ENAME from emp where DEPTNO=10;

ROWID              ENAME
------------------ ----------
AAAyd2AAHAAAAoeAAA SMITH
AAAyd2AAHAAAAoeAAD JONES
AAAyd2AAHAAAAoeAAH SCOTT
AAAyd2AAHAAAAoeAAK ADAMS
AAAyd2AAHAAAAoeAAM FORD

SQL> select ENAME,rowid from emp where DEPTNO=20
  2    minus
  3    select ENAME,rowid from emp where DEPTNO=10;

ENAME      ROWID
---------- ------------------
ADAMS      AAAyd2AAHAAAAoeAAK
FORD       AAAyd2AAHAAAAoeAAM
JONES      AAAyd2AAHAAAAoeAAD
SCOTT      AAAyd2AAHAAAAoeAAH
SMITH      AAAyd2AAHAAAAoeAAA

SQL>


Thumbs Up
Rajuvan

[Updated on: Mon, 26 November 2007 01:42]

Report message to a moderator

Re: order by in Minus Operator [message #283095 is a reply to message #283087] Mon, 26 November 2007 01:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Minus generally select the rows in the order it is selected.

This is wrong.
Minus does not imply any order.
Only ORDER BY returns sorted rows.

Regards
Michel
Re: order by in Minus Operator [message #283130 is a reply to message #283087] Mon, 26 November 2007 03:11 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
rajavu1 wrote on Mon, 26 November 2007 07:33


1. No need to use GROUP BY IDP055.ROWID,IDP010.AYBLNO if you want ot select IDP055.ROWID IDP010.AYBLNO . rather Use DISTINCT Keyword


Why use distinct?
WITH t AS (SELECT 1 x FROM dual
            UNION ALL
           SELECT 1 x FROM dual
           UNION ALL
           SELECT 1 x FROM dual
           UNION ALL 
           SELECT 2 x FROM dual
           UNION ALL 
           SELECT 3 x FROM dual
           UNION ALL 
           SELECT 4 x FROM dual)
,
    y as (SELECT 2 b FROM dual 
           UNION ALL 
          SELECT 4 FROM dual)
SELECT x FROM t
MINUS
SELECT b FROM y;
X                      
---------------------- 
1                      
3                      

2 rows selected

All set operators (with the exception of UNION ALL) get rid of duplicates, therefore distinct is pointless.
Re: order by in Minus Operator [message #283142 is a reply to message #283079] Mon, 26 November 2007 03:31 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Yes Yes..

Point Noted . Smile

Thumbs Up
Rajuvan
Re: order by in Minus Operator [message #283151 is a reply to message #283079] Mon, 26 November 2007 03:51 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

But from My experience MINUS retrieves fields in ascending Order.
(Might be non-documented)

Eg :

SQL> select JOB,EMPNO from emp;

JOB            EMPNO
--------- ----------
SALESMAN        7369
SALESMAN        7499
SALESMAN        7521
MANAGER         7566
SALESMAN        7654
MANAGER         7698
MANAGER         7782
ANALYST         7788
PRESIDENT       7839
SALESMAN        7844
CLERK           7876
CLERK           7900
ANALYST         7902
CLERK           7934
CLERK           2000

15 rows selected.

SQL> select JOB,EMPNO from emp
  2  MINUS
  3  select NULL,NULL from DUAL;

JOB            EMPNO
--------- ----------
ANALYST         7788
ANALYST         7902
CLERK           2000
CLERK           7876
CLERK           7900
CLERK           7934
MANAGER         7566
MANAGER         7698
MANAGER         7782
PRESIDENT       7839
SALESMAN        7369
SALESMAN        7499
SALESMAN        7521
SALESMAN        7654
SALESMAN        7844

15 rows selected.

SQL>


Expert comments are welcome Smile

Thumbs Up
Rajuvan
Re: order by in Minus Operator [message #283155 is a reply to message #283151] Mon, 26 November 2007 03:55 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
The only way to get an ordered list is to use the order by clause. End of story. This is well documented.
The only way to get an ordered list is to use the order by clause.
I do not care what your query shows
The only way to get an ordered list is to use the order by clause.
Re: order by in Minus Operator [message #283167 is a reply to message #283155] Mon, 26 November 2007 04:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with data as 
  2    ( select 'ťa' from dual union all select 'eb' from dual )
  3  select * from data
  4  minus
  5  select null from dual
  6  /
'…
--
eb
ťa

SQL> with data as 
  2    ( select 'ťa' from dual union all select 'eb' from dual )
  3  select * from data
  4  order by 1
  5  /
'…
--
ťa
eb

Regards
Michel
Re: order by in Minus Operator [message #283211 is a reply to message #283079] Mon, 26 November 2007 06:34 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Nice !!!

Thumbs Up
Rajuvan.
Re: order by in Minus Operator [message #283227 is a reply to message #283079] Mon, 26 November 2007 06:58 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Is there any exception when we do ORDER BY character(VARCHAR2-field)?

I just want to confirm.

Regards,
Kiran.
Re: order by in Minus Operator [message #283237 is a reply to message #283227] Mon, 26 November 2007 07:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, there is no exception.
Varchar2 examples are easier to build, that's all.
You can build the same kind of things with numbers.

Regards
Michel
Re: order by in Minus Operator [message #283349 is a reply to message #283237] Mon, 26 November 2007 19:54 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Just to round this one out:

MINUS currently employs a Binary Sort, whereas ORDER BY performs an NLS_SORT / NLS_LANGUAGE sort, which may be specified as Binary, or indistinguishable from binary for most data.

I'm not sure how Michel would replicate his excellent example with numbers - I thought NLS only controlled position and characters used for separators - and that binary values would correspond to absolute values.....

There is no guarantee that Oracle will continue to use a Binary Sort for MINUS (or INTERSECT or UNION) in the future.

10.2 is a good example of this, and sorts are no longer always used to resolve GROUP BY like they used to.

Even if you are happy with binary sorts and not planning to upgrade ever, parallel query may still bring you unstuck. I haven't checked in this case, but in many operations it partitions the data and performs the operation separately on each partition.

In the case of MINUS, that could cause the rows of each partition to return in sorted order, but the overall order of partitions would be random.

Ross Leishman
Re: order by in Minus Operator [message #283359 is a reply to message #283079] Mon, 26 November 2007 22:58 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

One more doubt, when we store numbers in VARCHAR2 field and sort it using ORDER BY cluase, then the sorted output returns how much true?

Somwhere i heard/read we better convert it into number(TO_NUMBER) and then should do sorting, ORDER BY.

What would be the difference here?

Regards,
Kiran.
Re: order by in Minus Operator [message #283364 is a reply to message #283079] Mon, 26 November 2007 23:12 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>What would be the difference here?
You should construct 2 simple test cases; one using NUMBER & the other using VARCHAR2 in order to answer your own question.
Re: order by in Minus Operator [message #283367 is a reply to message #283359] Mon, 26 November 2007 23:24 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
I would strengthen this advice: NEVER store numbers as VARCHAR2.
You shall enforce, it contains ONLY numeric characters (what about decimal point character, thousand separator, scientific notation?), otherwise using TO_NUMBER will end with an error.

This is a common principle, simple difference between alphabetic (for VARCHAR2) and numerical (for NUMBER) order.
Re: order by in Minus Operator [message #283370 is a reply to message #283079] Mon, 26 November 2007 23:33 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

There will be a difference.

SQL> CREATE TABLE test_ord (col VARCHAR2(10));

Table created.

SQL> INSERT INTO test_ord
  2              (col
  3              )
  4       VALUES ('121'
  5              );

1 row created.

SQL> INSERT INTO test_ord
  2              (col
  3              )
  4       VALUES ('0123'
  5              );

1 row created.

SQL> INSERT INTO test_ord
  2              (col
  3              )
  4       VALUES ('0111'
  5              );

1 row created.

SQL> INSERT INTO test_ord
  2              (col
  3              )
  4       VALUES ('3'
  5              );

1 row created.

SQL> INSERT INTO test_ord
  2              (col
  3              )
  4       VALUES ('09'
  5              );

1 row created.

SQL> COMMIT ;

Commit complete.

SQL> SELECT   *
  2      FROM test_ord
  3  ORDER BY col;

COL
----------
0111
0123
09
121
3

SQL> SELECT   *
  2      FROM test_ord
  3  ORDER BY TO_NUMBER (col);

COL
----------
3
09
0111
121
0123


Regards,
Kiran.
Re: order by in Minus Operator [message #283371 is a reply to message #283079] Mon, 26 November 2007 23:36 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>There will be a difference.
which result is correct?
Re: order by in Minus Operator [message #283374 is a reply to message #283079] Mon, 26 November 2007 23:50 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

See ,

In the first query it is sorted in Order of Character.
Quote:

COL
----------
0111
0123
09
121
3


while it is sorted in Order of Number in case of second query
(3,9,111,121,123)

Quote:

3
09
0111
121
0123


Anyway its not adviced to store Numbers in CARCHAR2 field.

Thumbs Up
Rajuvan

Re: order by in Minus Operator [message #283376 is a reply to message #283079] Tue, 27 November 2007 00:01 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Yes that i got it.

The character will do order start from first position of each number. If the first postion has same number then looking for second position.

Kiran.
Re: order by in Minus Operator [message #283378 is a reply to message #283079] Tue, 27 November 2007 00:06 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Yes.. Frst and sencod positions are specific to you example only Smile

But oracle Orders based on the order by clause only

Thumbs Up
Rajuvan
Re: order by in Minus Operator [message #283384 is a reply to message #283378] Tue, 27 November 2007 00:17 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Can't you guys just talk to each other instead of taking over each thread?
Re: order by in Minus Operator [message #283394 is a reply to message #283384] Tue, 27 November 2007 00:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I already asked them to do so in another topic.
It seems they can't.
It seems the only way they can talk each other is with OraFAQ.
OraFAQ the new chatroom for neighbours!

Regards
Michel
Re: order by in Minus Operator [message #283396 is a reply to message #283349] Tue, 27 November 2007 00:44 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ross,

It is possible to replace the example with numbers using partitioned tables and/or parallelism.
I'm pretty confident we can do it with clustered tables.
This is why I said it is not so easy to build an example with numbers.

Regards
Michel
Previous Topic: USING TO_NUMBER and GETTING INVALID NUMBER ERROR
Next Topic: find check constraint what i have in specific column from data dictionary
Goto Forum:
  


Current Time: Sat Dec 10 20:26:03 CST 2016

Total time taken to generate the page: 0.08154 seconds