Get the nearest higher and lower rows in SQL

Question

I'm trying to select the first 5 amounts above 2000 and the first 5 below 2000 and return the data in a single resultset.

SELECT TOP 5 * 
FROM   bid 
WHERE  amount >= 2000 
ORDER  BY amount ASC 
UNION
SELECT TOP 5 * 
FROM   bid 
WHERE  amount < 2000 
ORDER  BY amount DESC

Looks like this is not how union is supposed to be used as I'm getting a runtime error.

Incorrect syntax near the keyword 'union'.

What's the correct way of writing the desired query?

Please tag your question with the DBMS you're using, as they differ in details like this. — Barmar, Jan 09 '14 at 05:59
The "order by"s are what's getting you; see here: http://stackoverflow.com/questions/4715820/how-to-order-by-with-union — Bandrami, Jan 09 '14 at 06:03

score 3 · Accepted Answer · edited Jan 09 '14 at 09:03

SELECT * 
FROM   (SELECT TOP 5 * 
        FROM   bid 
        WHERE  amount >= 2000 
        ORDER  BY amount ASC) t1 
UNION 
SELECT * 
FROM   (SELECT TOP 5 * 
        FROM   bid 
        WHERE  amount < 2000 
        ORDER  BY amount DESC) t2

You can add additional order to the results:

SELECT * 
FROM   (SELECT * 
        FROM   (SELECT TOP 5 * 
                FROM   bid 
                WHERE  amount >= 2000 
                ORDER  BY amount ASC) t1 
        UNION 
        SELECT * 
        FROM   (SELECT TOP 5 * 
                FROM   bid 
                WHERE  amount < 2000 
                ORDER  BY amount DESC) t2) t3 
ORDER  BY t3.amount

Get the nearest higher and lower rows in SQL

1 Answers1