mpdillon
asked on
Union - TOP and Bottom
The select statements:
Select Top(200) ID, ColA - ColB From Table
Order by ColA-ColB Asc
and
Select Top(200) ID, ColA - ColB From Table
Order by ColA-ColB Desc
yield the correct results for the top and bottom 200 records.
I would like to combine the two queries into a single query. I have tried to use a Union.
Select Top(200) ID, ColA - ColB From Table
Order by ColA-ColB Asc
UNION
Select Top(200) ID, ColA - ColB From Table
Order by ColA-ColB Desc
but the UNION fails because of the Order By clauses.
How should I query the top 200 and bottom 200 records in a table with just one query?
Thanks,
pat
Select Top(200) ID, ColA - ColB From Table
Order by ColA-ColB Asc
and
Select Top(200) ID, ColA - ColB From Table
Order by ColA-ColB Desc
yield the correct results for the top and bottom 200 records.
I would like to combine the two queries into a single query. I have tried to use a Union.
Select Top(200) ID, ColA - ColB From Table
Order by ColA-ColB Asc
UNION
Select Top(200) ID, ColA - ColB From Table
Order by ColA-ColB Desc
but the UNION fails because of the Order By clauses.
How should I query the top 200 and bottom 200 records in a table with just one query?
Thanks,
pat
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Select ID, PriceDiff From
(Select Top(200) ID, (ColA-ColB) as PriceDiff
From Table
Order by PriceDiff Asc) A
UNION
(Select Top(200) ID, (ColA-ColB) as PriceDiff
From Table
Order by PriceDiff Asc) B
Order by PriceDiff Desc
in SQL 2008 R2