CODEWITHSUNDEEP
sql-serverunion
Taqi Gates
Taqi Gates

Reputation: 87

order by clause in union statement

I have some records coming from 4 tables and date is not the common field in all of them but I use 'as'. Now I need to order by date 

select id,convert(varchar(20), SoldDate,3) as Date from sale 
union
select id,convert(varchar(20), PaymentDate,3) as Date from purchase 
union 
select id,convert(varchar(20), PaymentClearedDate,3) as Date from payments 
union 
select id,convert(varchar(20), PaymentClearedDate,3) as Date from orders    
order by Date desc

I need order by Date

Upvotes: 2

Views: 61

Answers (2)

Cato
Cato

Reputation: 3701

What goes wrong with your code? You are ordering by Date there as far as I can see. If you want to order by the Dates in date order, create another column which is the date, as a date type, e.g.

select id,convert(varchar(20), SoldDate,3) as Date,SoldDate as d2 from sale 
union
select id,convert(varchar(20), PaymentDate,3) as Date, PaymentDate as d2  from purchase 
union 
select id,convert(varchar(20), PaymentClearedDate,3) as Date, PaymentClearedDate as d2 from payments 
union 
select id,convert(varchar(20), PaymentClearedDate,3) as Date, PaymentClearedDate as d2 from orders    
order by d2  desc

you might need to cast your dates to type date if they are stored in some other text format e.g.

select id,convert(varchar(20), SoldDate,3) as Date,CAST(SoldDate as datetime2) as d2 from sale 
union
...etc
order by d2  desc

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use CTE or subquery : 

SELECT t.*
FROM ( <Query>
     ) t
ORDER BY r.Date DESC;

However, i would argue on date conversations, if you want just date then use cast(SoldDate as date) & latter convert it to dd\MM\yy.

So, your updated query would be :

SELECT t.id, CONVERT(VARCHAR(10), t.[Date], 3) AS [Date]
FROM (SELECT id, CAST(SoldDate AS DATE) AS [Date]
      FROM sale 
      UNION
      SELECT id, CAST(PaymentDate AS DATE) 
      FROM purchase  
      UNION
      . . . 
     ) t
ORDER BY t.[Date] DESC;

Upvotes: 3

Related Questions