Yonah's SQL Tip of the Week #1 - Top and Sorting Views

I am sure that you've stumbled across the Top command. You might have seen it by looking at the SQL statement when browsing tables in Enterprise Manager. You may have seen it in the Syntax for SELECT. No matter what the Top command is a very useful command in more ways than one.

Essentially Top lives up to its name. The most Common syntax is:
SELECT TOP n * from table order by sales_count DESC

The query above will return the first n rows of the result set. This type of query can be useful in many situations. For example, when you are outputting the top ten selling products on your website. Notice that the ORDER BY clause is set. Order by essentially determines the order in which the rows are returned, and since we are filtering out the top 10, it also controls which records get displayed. Without Order By, Top would only return the first ten records in the result set.

Top also has another mode - percent. Instead of returning a fixed number of rows, percent returns the top n percent of a result set. This can be good if you want to find, say, the top ten percent of the most frequently sold items:

Select top 10 percent * from products, order by sales_count desc


The Percent mode will work with any percentage, up to an including 100 Percent. Why would you want to put top 100 percent in a query if not specifying top at all gives the same result? Well I could definitely think of one main reason for this - Creating a View with a Default Sort. In SQL, views cannot include an order by clause, unless the TOP keyword is specified. Obviously, this needs to be there in order to display the appropriate records. But let's say that you want to pre-sort an entire result set in a view without specifying ORDER BY in your query? Since you can't put an Order By clause into the without specifying TOP, simply put the following SQL into your view:

Create View myView as
Select TOP 100 percent from table, order by sales_count DESC

Now you have a view that is pre-sorted.