How to use order by in union all

Question

Vendor	Date of Last Check	Date of check for month	Status	Amount
Darren Gygi Home Collection				0
Fiddler's Elbow				0
Honey & Me				0
Posies and Such Novelty Paper Goods				0
Rivers Edge Products				0
Wheelhouse Designs				0
Woodlink				0
American Silk Flower	11/14/2017		Hold	72.75
Boston Warehouse	11/13/2017		Hold	30
Total Amount	NULL	NULL	TOTAL	102.75

 

Hi,

I want to add order by in first statement .How to use this.In 2nd statement Total Amount should not order.How is it possible?Can any one help me?

 

select Vendor,MAX([Date of Last Check]) 'Date of Last Check','' as 'Date of check for month',Status,SUM(Amount) 'Amount' from

(SELECT Vendor,CASE WHEN CONVERT(varchar(50), DateModified, 101)='01/01/1900' THEN '' ELSE CONVERT(varchar(50), DateModified, 101) end

as 'Date of Last Check', CASE WHEN CONVERT(varchar(50), PaycommissionDate, 101)='01/01/1900' THEN '' ELSE CONVERT(varchar(50), PaycommissionDate, 101) end

AS 'Date of check for month',CASE STATUS WHEN 'INPROGRESS' THEN '' WHEN 'SAVED' THEN 'Hold' WHEN 'FINISHED' THEN 'Confirmed' END as Status,SUM([Commission $ paid]) AS 'Amount'

 

 

FROM dbo.Tbl_Commission_Reconcilation

WHERE (Vendor IS NOT NULL)

GROUP BY Vendor, PaycommissionDate, CONVERT(varchar(50), DateModified, 101),dbo.Tbl_Commission_Reconcilation.Status

)a group by Vendor,a.Status --order by Vendor

 

UNION ALL

select 'Total Amount' Vendor,NULL '[Date of Last Check]',NULL 'Date of check for month','TOTAL' 'STATUS',SUM([Commission $ paid]) 'Amount' from Tbl_Commission_Reconcilation

where Vendor is not null 

 

Thanks in advance 

Answer 1

hi,

if you wanted to do order by in union then you should do at end of the statement only, you can not add in above statements,

you can do like this, first select statement with order by insert in one temporary table and second select statement insert without order by then select * from that temporary table.