Home » SQL & PL/SQL » SQL & PL/SQL » Question on ORDER BY (Oracle 9.2.0.3/Unix)
Question on ORDER BY [message #349086] Thu, 18 September 2008 18:12 Go to next message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
I have a query which utilizes the WITH clause and COUNT/OVER/PARTITION clause and GROUP BY function
And am puzzled over not being able
to get output in the order I expect.

While I go back to researching the involved material, I thought I'd post the situation to see if my weary eyes may be missing something else.

The Query:

/* Formatted on 2008/09/18 18:18 (Formatter Plus v4.8.7) */
WITH t2 AS
     (
        SELECT   edp_claim_id,
                 provider_match_code,
                 COUNT (*) OVER (PARTITION BY provider_match_code ORDER BY provider_match_code)
                                                                     AS prcsd
        FROM     (SELECT   *
                  FROM     edp_hcfa_claim_task3_2n
                  ORDER BY provider_match_code)
        WHERE    SUBSTR (provider_match_code, 1, 1) IN ('N', 'C')
        ORDER BY provider_match_code),
     t3 AS
     (
        SELECT   edp_claim_id,
                 provider_match_code,
                 COUNT (*) OVER (PARTITION BY provider_match_code ORDER BY provider_match_code)
                                                                     AS prcsd
        FROM     (SELECT   *
                  FROM     edp_hcfa_claim
                  ORDER BY provider_match_code)
        WHERE    SUBSTR (provider_match_code, 1, 1) IN ('T', 'N', 'C')
        ORDER BY provider_match_code),
     base AS
     (
        SELECT   edp_claim_id,
                 provider_match_code,
                 COUNT (*) OVER (PARTITION BY provider_match_code ORDER BY provider_match_code)
                                                                     AS prcsd
        FROM     (SELECT *
                  FROM   edp_hcfa_claim_task3_bkup)
        WHERE    SUBSTR (provider_match_code, 1, 1) IN ('N', 'C')
        ORDER BY provider_match_code)
SELECT   b.provider_match_code as CDE,
         b.prcsd,
         'PRD' AS type
FROM     base b
GROUP BY b.provider_match_code,
         b.prcsd
UNION ALL
SELECT   t2.provider_match_code,
         t2.prcsd,
         'VPL'
FROM     t2
GROUP BY t2.provider_match_code,
         t2.prcsd
UNION ALL
SELECT   t3.provider_match_code,
         t3.prcsd,
         'NEW'
FROM     t3
GROUP BY t3.provider_match_code,
         t3.prcsd



The output:
CDE        PRCSD                                  TYPE 
NT1        23181                                  PRD  
NT3        14321                                  PRD  
CST1       463                                    PRD  
CST2       301                                    PRD  
CST3       944                                    PRD  
NZT1       3600                                   PRD  
NZT3       2241                                   PRD  
NACS1      730                                    PRD  
NACS3      295                                    PRD  
NCST3      1178                                   PRD  
NT1        2653                                   VPL  
NT3        1791                                   VPL  
CST1       53                                     VPL  
CST3       2                                      VPL  
NZT1       391                                    VPL  
NZT3       238                                    VPL  
NACS1      129                                    VPL  
NACS3      127                                    VPL  
NCST3      157                                    VPL  
NT1        486                                    NEW  
NT3        481                                    NEW  
TLZ        87                                     NEW  
CST1       1                                      NEW  
CST3       2                                      NEW  
NZT1       90                                     NEW  
NZT3       25                                     NEW  
TLA8       1022                                   NEW  
TLCS       283                                    NEW  
TNA8       5191                                   NEW  
TNCS       1574                                   NEW  
NACS1      73                                     NEW  
NACS3      127                                    NEW  
NCST3      14                                     NEW  
TLZA8      5                                      NEW  
TNZA8      24                                     NEW  
TLCSA8     1                                      NEW  
TNCSA8     21                                     NEW 


Expected Results:

CDE        PRCSD                                  TYPE
CST1       463                                    PRD  
CST2       301                                    PRD  
CST3       944                                    PRD  
NACS1      730                                    PRD  
NACS3      295                                    PRD  
NCST3      1178                                   PRD  
{...}  


Just puzzled as to why I do not have this in CDE order.

Tried just one SELECT, removing the UNIONs:

SELECT   b.provider_match_code as CDE,
         b.prcsd,
         'PRD' AS type
FROM     base b
WHERE EXISTS (SELECT 1 FROM T2,T3) GROUP BY b.provider_match_code,
         b.prcsd


Still N before C
NT1
NT3
CST1

ORDER BY in the SELECT with UNION through a syntax error.

Anyway this is just a learning exercise for me
(one of those humbling ones),
I know how to get around the problem
I just dont understand why the ORDER
isnt functioning as I expect from this structure.

Eyes grow tired and untrustworthy, time to go home.

Just didnt know if something sticks out
to a second pair of eyes that my brain doesnt want
to compute today. I'll hit the reference links
after a nap for the funcional components involved.

Not a roadblock for what I am trying to do, just a puzzle.

just as an fyi -

It must appear odd to see the WITH query subfactoring without
Aggregation and then use the aggregate function in the SELECT portion. This is just the start of working some thoughts out
for a more complex data analysis. the WITH was for my eyes Cool


Thanx
Harry

Re: Question on ORDER BY [message #349098 is a reply to message #349086] Thu, 18 September 2008 20:59 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Unless you include an order by clause in the outer query, Oracle does not guarantee the order of the results returned. When you use union all with mismatched or missing column aliases, you have to order by column position instead of column name, but then you cannot use decode or case in your order by. To get around this you can put the whole thing in a subquery, then order it using column names and decode and you can eliminate the useless order by and group by clauses elsewhere in the query. Please see the similar reproduction and solution below.

-- reproduction:
SCOTT@orcl_11g> WITH t2 AS
  2  	  (
  3  	     SELECT   deptno,
  4  		      job,
  5  		      COUNT (*) OVER (PARTITION BY job ORDER BY job)
  6  									  AS prcsd
  7  	     FROM     (SELECT	*
  8  		       FROM	emp
  9  		       ORDER BY job)
 10  	     WHERE    SUBSTR (job, 1, 1) IN ('M', 'C')
 11  	     ORDER BY job),
 12  	  t3 AS
 13  	  (
 14  	     SELECT   deptno,
 15  		      job,
 16  		      COUNT (*) OVER (PARTITION BY job ORDER BY job)
 17  									  AS prcsd
 18  	     FROM     (SELECT	*
 19  		       FROM	emp
 20  		       ORDER BY job)
 21  	     WHERE    SUBSTR (job, 1, 1) IN ('S', 'M', 'C')
 22  	     ORDER BY job),
 23  	  base AS
 24  	  (
 25  	     SELECT   deptno,
 26  		      job,
 27  		      COUNT (*) OVER (PARTITION BY job ORDER BY job)
 28  									  AS prcsd
 29  	     FROM     (SELECT *
 30  		       FROM   emp)
 31  	     WHERE    SUBSTR (job, 1, 1) IN ('M', 'C')
 32  	     ORDER BY job)
 33  SELECT   b.job as CDE,
 34  	      b.prcsd,
 35  	      'PRD' AS type
 36  FROM     base b
 37  GROUP BY b.job,
 38  	      b.prcsd
 39  UNION ALL
 40  SELECT   t2.job,
 41  	      t2.prcsd,
 42  	      'VPL'
 43  FROM     t2
 44  GROUP BY t2.job,
 45  	      t2.prcsd
 46  UNION ALL
 47  SELECT   t3.job,
 48  	      t3.prcsd,
 49  	      'NEW'
 50  FROM     t3
 51  GROUP BY t3.job,
 52  	      t3.prcsd
 53  /

CDE            PRCSD TYP
--------- ---------- ---
MANAGER            3 PRD
CLERK              4 PRD
MANAGER            3 VPL
CLERK              4 VPL
SALESMAN           4 NEW
MANAGER            3 NEW
CLERK              4 NEW

7 rows selected.


-- solultion:
SCOTT@orcl_11g> SELECT * FROM
  2    (WITH t2 AS
  3  	  (
  4  	     SELECT   deptno,
  5  		      job,
  6  		      COUNT (*) OVER (PARTITION BY job ORDER BY job)
  7  									  AS prcsd
  8  	     FROM     emp
  9  	     WHERE    SUBSTR (job, 1, 1) IN ('M', 'C')),
 10  	  t3 AS
 11  	  (
 12  	     SELECT   deptno,
 13  		      job,
 14  		      COUNT (*) OVER (PARTITION BY job ORDER BY job)
 15  									  AS prcsd
 16  	     FROM     emp
 17  	     WHERE    SUBSTR (job, 1, 1) IN ('S', 'M', 'C')),
 18  	  base AS
 19  	  (
 20  	     SELECT   deptno,
 21  		      job,
 22  		      COUNT (*) OVER (PARTITION BY job ORDER BY job)
 23  									  AS prcsd
 24  	     FROM     emp
 25  	     WHERE    SUBSTR (job, 1, 1) IN ('M', 'C'))
 26    SELECT	DISTINCT b.job as CDE,
 27  		b.prcsd,
 28  		'PRD' AS type
 29    FROM	base b
 30    UNION ALL
 31    SELECT	DISTINCT t2.job,
 32  		t2.prcsd,
 33  		'VPL'
 34    FROM	t2
 35    UNION ALL
 36    SELECT	DISTINCT t3.job,
 37  		t3.prcsd,
 38  		'NEW'
 39    FROM	t3)
 40  ORDER BY DECODE (type, 'PRD', 1, 'VPL', 2, 'NEW', 3), cde
 41  /

CDE            PRCSD TYP
--------- ---------- ---
CLERK              4 PRD
MANAGER            3 PRD
CLERK              4 VPL
MANAGER            3 VPL
CLERK              4 NEW
MANAGER            3 NEW
SALESMAN           4 NEW

7 rows selected.

SCOTT@orcl_11g> 


Re: Question on ORDER BY [message #349100 is a reply to message #349086] Thu, 18 September 2008 21:31 Go to previous message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
Thank you Barbara, I very much appreciate your extra time
in replicating the scenario with the solution as well as
the explanation.

Best Regards!
Harry
Previous Topic: XML parsing function
Next Topic: regarding a query
Goto Forum:
  


Current Time: Sat Dec 10 05:00:46 CST 2016

Total time taken to generate the page: 0.20679 seconds