Sorting Data

only for RuBoard

Now that you know how to get and limit the amount of rows returned in a result set to only the rows you need, let's talk a little about how to organize the data.

The ORDER BY Clause

The ORDER BY clause specifies the sort of a result set using one or more column names separated by commas. The sort is how the rows are organized; for instance, you can sort the result set in a query to the Products table so the ProductName column is listed in alphabetical order.

Note

Sort order cannot be used with SubQueries or to sort data of the type nText , Text , or image .


The sort can be both alphabetical and/or chronological. The following example returns all the rows in the Products table and orders them by the ProductName column in alphabetical order:

 1: SELECT * 2: FROM Products 3: ORDER BY ProductName 

You can alter the order further by specifying that you want the data in ascending or descending order. The default order is ascending . Adding the DESC argument changes the result set to display data in descending order. In the following example it's reverse alphabetical order:

 1: SELECT * 2: FROM Products 3: ORDER BY ProductName DESC 

If you want to hard-code ascending order, you can use the argument ASC . You also can specify more than one column in the sort order, as the following code example demonstrates :

 1: SELECT * 2: FROM Products 3: ORDER BY ProductName DESC, SupplierID 

In a situation like this, all rows and columns are returned in the result set. First, the result set is sorted in descending order determined by the ProductName column, and then its additionally sorted by SupplierID column in ascending order within each product. This means that if you have two products with the same name , such as "peanuts," they will be returned with each other in the result set. But you also specified that the results should be ordered by SupplierID , so if Company A has a SupplierID of 1 and Company B has a SupplierID of 5, Company A will be listed in the result set prior to Company B.

The GROUP BY Clause

The GROUP BY clause can be used to summarize data based on particular columns. The following example contains a SQL statement that will return a list of all company names and a total of how many products they currently have available. Additionally, it will sort the results by company name in ascending order:

 1: SELECT Suppliers.CompanyName AS [Company Name], COUNT(Products.ProductName) AS 2: [Number Of Products] 3: FROM 4: Products 5: INNER JOIN 6: Suppliers 7: ON 8: Suppliers.SupplierID = Products.SupplierID 9: GROUP BY Suppliers.CompanyName 10: ORDER BY Suppliers.CompanyName ASC 

The preceding example uses an aggregate function named COUNT , which returns the number of items in a group. In this example, it counts the number of different products for a particular company.

Notice also that all of the columns are given human-readable names. The CompanyName column is given the name "Company Name," and the total number of products returned by your Count function is given the name "Number of Products". This is a personal choice and isn't necessary. In my personal experience, though, it has saved time on the front end. If the result set is going to be shown on a Web page or any other type of presentation layer, you don't want the column headers to look like they do in the database, so you can either hard-code new ones on the front end or take care of it on the back-end in your SQL statement, or do nothing. If you rename them in the query, you don't have to worry about doing it later, and if your result set is used in many places you can change the query once in your stored procedure and the changes will be reflected on every page.

If you would like to see an example of what I am talking about, run the preceding query without the AS clause and check out what the column names are. Then do the same with the AS clause.

Okay, on with the statement. Because you're retrieving data from two tables, you're performing an INNER JOIN and the two columns you're comparing for the join are the SupplierID columns. Once those are returned, you tell the result set that you want it to be grouped by the CompanyName column. Hence, it will add up all the products for that company name and sort the whole result set in alphabetical order by the company name.

only for RuBoard


Programming Data-Driven Web Applications with ASP. NET
Programming Data-Driven Web Applications with ASP.NET
ISBN: 0672321068
EAN: 2147483647
Year: 2000
Pages: 170

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net