How can I use aggregate Functions in UNION ALL Resultset
FOR EXAMPLE
SELECT A,B FROM MyTable UNION ALL SELECT B,C FROM MYAnotherTable
Result Set Would Be
A B -------------- 1 2 3 4 4 5 6 7
When I tried to get MAX(A)
it returns 3
. I want 6
.
When I tried to get MAX(B)
it returns 4
. I want 7
.
Other than Max()
, Can I get another aggregate function which user defined?
For example:
(SELECT TOP 1 A WHERE B=5
)
Real Case Here
Advertisement
Answer
Try this way:
select max(A) from( SELECT A,B FROM MyTable UNION ALL SELECT B,C FROM MYAnotherTable ) Tab
SQL Fiddle DEMO
If the column A
is varchar (You said that in the comment below) try this way:
select max(A) from( SELECT cast(A as int) as A,B FROM MyTable UNION ALL SELECT B,C FROM MYAnotherTable ) Tab
With TOP 1
select max(A) from( SELECT top 1 cast(A as int) as A,B FROM MyTable UNION ALL SELECT B,C FROM MYAnotherTable ) Tab