We help IT Professionals succeed at work.

Output the results of two queries in SQL Server

185 Views
Last Modified: 2010-03-19
If I used the following to determine the cost of two objects, how would I output the difference?

select *
from (
select top 1 productprice
from products
order by productprice desc
) X
union all
select *
from (
select top 1 productprice
from products
order by productprice asc
) Y
Comment
Watch Question

Like this you mean.

select X.productprice - Y.productprice As productpricedifference
from (
select top 1 productprice
from products
order by productprice desc
) X
union all
select *
from (
select top 1 productprice
from products
order by productprice asc
) Y

Author

Commented:
Got the following error:

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Y.price" could not be bound.
Msg 205, Level 16, State 1, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
Sorry use this and try

select x.productprice - y.productprice From ( select top 1 productprice  from products
order by productprice desc) x,
(select top 1 productprice  from products
order by productprice asc ) y

Author

Commented:
same error
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Commented:
Did you get this worked out?
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.