Link to home
Start Free TrialLog in
Avatar of mpdillon
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
ASKER CERTIFIED SOLUTION
Avatar of Forefront_Data_Solutions
Forefront_Data_Solutions
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mpdillon
mpdillon

ASKER

Here is what I actually settled on:
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