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.
No comments:
Post a Comment