1

Consider a table A and table B like :

Table A:

debit   credit  row
-----------------------
10      0       1
0       10      1
20      0       2
0       20      2
30      0       3
0       30      3

Table B:

debit   credit  row
-----------------------
10      0       1
0       10      1
20      0       2
0       20      2
30      0       3
0       30      3

Result:

debit   credit  row
--------------------
10      0       1
20      0       2
30      0       3
0       10      1
0       20      2
0       30      3

I'm trying to union all table A, B and show debit first, then sort it by row column.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Elham Azadfar
  • 709
  • 2
  • 17
  • 34
  • 1
    It’s unclear how the data should be handled. First you had no zeroes. Now you have two tables with the same data and output has no union of this, just the same data sorted differently. Please put actual input and actual output and explain how they relate to each other. – Sami Kuhmonen Dec 24 '17 at 06:30

1 Answers1

2

by definition, the individual SELECTs making up a UNION are not allowed to contain an ORDER BY clause. The only ORDER BY clause allowed is at the end of the UNION and it applies to the entire UNION, making xxx UNION yyy ORDER BY zzz the eqivalent of (xxx UNION yyy) ORDER BY zzz

Meaning:

Invalid: 

Select debit,credit,row
from
(
   Select debit,credit,row
   From table a
   Where 'condition'
  Union
   Select debit,credit,row
   From table b
   Where 'condition 2'
) results
order by debit, row

Valid: 

Select debit,credit,row
From table a
Where 'condition'
Union
Select debit,credit,row
From table b
Where 'condition 2'
Order by debit, row
Barr J
  • 10,636
  • 1
  • 28
  • 46
  • Thanks for your help, would you please see this question too https://stackoverflow.com/questions/47958656/order-by-x-then-order-by-y-column-in-sql-server – Elham Azadfar Dec 24 '17 at 06:56