Returning TOP and BOTTOM Rows in SQL

The other day I saw a request come across a forum that was asking how to get the top 1 and the bottom 1 of customers with most and least sales. It seemed easy enough, after all it is just use a simple union. Something like this:

Select top (1) * FROM #Sales order by Sales desc
Union All
Select top (1) * FROM #Sales order by Sales asc

However; if you run this you will get an error like this:

Incorrect syntax near the keyword ‘Union’.

The way to get around this problem is to use a derived table, similar to a temporary table. Below is a sample of how this would work using a derived table.

Author Spotlight

David Bauernschmidt

I live in the historical triangle of Virginia where I am married with two daughters. I have spent over 13 years working for a Fortune 500 company in the computer area. I started in VB 6.0 and by the time I ended my employment I was supervising a development team where we built many web applications. When my first daughter was born I wanted to spend more time with her so I left and became a programmer analyst for local government as well as launch my own company. Since then I have grown James River Webs into a profitable web design and application company helping small businesses create a big presence on the internet. As an employee I have created web application used by citizens and other companies. I enjoy fly fishing, and spending time with my family. I also enjoy learning new approaches and development tools when it comes to developing applications.