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.


CREATE TABLE #Sales(
 Customerid int,
 Sales decimal(5,2))

 Insert into #Sales Values (1,300)
 Insert into #Sales Values (2,87.56)
 Insert into #Sales Values (3,17.34)
 Insert into #Sales Values (4,-16.44)
 Insert into #Sales Values (5,304)
 Insert into #Sales Values (6,0)
 Insert into #Sales Values (7,564.34)
 Insert into #Sales Values (8,76.35)
 Insert into #Sales Values (9,123.55)
 Insert into #Sales Values (10,123.67)

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

Drop table #sales

If want to test this out try this.

Looking for quality web hosting? Look no further than Arvixe Web Hosting!

Tags: , , , , , , , , | Posted under Programming/Coding | RSS 2.0

Author Spotlight

David Bauernschmidt

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.

Leave a Reply

Your email address will not be published. Required fields are marked *


3 + 2 =

You may use these HTML tags and attributes: <a href="" title="" rel=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>