April 10, 2011

SQL 2005 - Group by, Order by, Having, Top With Ties Clause in SQL Server 2005

1. Introduction

In this article, we will explore the usage of the "Group By" and "Order By" clause. Also, we will see how does the TOP option behave with the Order by clause.

2. Example 01 – Order by

The below Example shows, the contact title is sorted in ascending order. This is done by using the Order By ContactTitle which is shown in the below example as the last line of query. The default is the ascending order.

3. Example 02 – Order by descending

The above query sorted the result in the Ascending order. Now look at the below example which sorts the same contact title column in descending order. We need to explicitly specify the "DESC" keyword in the Order By Clause.

4. Example 03 – Order by with more than one column

Now Look at the below example.

The query sorted the result based on two columns. First, it sorted the contact title in the ascending order and with-in that it sorted the result in descending order for the column Customer Id. The order by clause order by ContactTitle Asc, CustomerID Desc; in the above query has two columns, Contact Title first and Customer id next. Above screen shot shows [Red box] Contact title is sorted in ascending order. Look at the blue box; the result is sorted in descending order within the Marketing Assistance value of the Contact title.

5. Example 04 – Order by with Top

Now look at the below example that shows the usage of the "Top" clause in conjunction with the Order by clause.

In the above Query, we asked for top 10 records. SQL server first performs the sorting and then picks the top 10 records from it. The box shown in red indicates that it is not the complete records for the country Brazil. The next query shows how do you bring complete records for the column specified in the Order by clause.

6. Example 05 - Select top with ties

As discussed in the previous example, to bring the complete records for the country (used in the order by) that participate in the top 10, use the top 10 "with ties" option. Below is the query output:

7. Example 06 – Aggregate Function

"Aggregate functions" operate on the group of records and if a grouping is not specified it is applied to the entire records of a given table. The below example shows the usage of the count() function that tells how many records exists in the table in which it is called.

So, the result says product table has 77 records. You can also use the other aggregate function SUM, AVG for average, Min, Max, Count(*) for total records and Count(<column_name>) for count of Number rows which does not have null value for the <column_name>

8. Example 07 – Aggregate Function with where clause

The below example shows the usage of the aggregate function count on a query that used a “where” clause in it. First, the record filter is applied, then a count on the column Discontinued is calculated skipping the null entries in it during the count.

9. Example 08 – One more Example for Aggregate

The example below is same as the previous one except that we used a different aggregate function. The query is to get the to Unit price total of the product that falls under the category 2.

10. Example 09 – Aggregate with group by

All the aggregate function example shown above is applied to all the records returned by the query. So it considers the whole record set as a group and applied the grouping function such as sum, Min etc on it. The group by clause is used to get sub-group of the whole record set returned by the query.

The below query first groups all the records returned based on the Category Id. So if there are 3 categories then there will three subgroups. The count(ProductId) aggregate is applied on each group. So the result shows you number of product available in each category.

11. Example 10 – Aggregate with group by and Having clause

Have a look at the result of the above query. Now we will filter the product category, which has product count more than 10. As you know the count is an aggregate (Applied onset of records) & we cannot use a where clause to apply the filter on count aggregate. The filter should be performed on the on the group level.  The having clause works similar to the where clause. Simply, where clause is for filtering the records and "having clause" is to filter the sub-groups returned.

The below query will return the category which has more than 10 products.

12. Example 11 – How it works all together

Have a look at the below query and the result. There is nothing special in it. But, it has all you learnt in this article.

Below is the short explanation on how SQL server sees the above query and retrieves the data from the database:

  1. First, a column filter is applied and it takes only two columns CategoryID and ProductId.
  2. Then the Record filter is applied on the records using the condition specified in the where clause.
  3. Now the Filtered column and records formed in sub – groups using the CategoryId as the Group.
  4. After the group, a Not NULL count on ProductID on each sub-group based on CategoryId is calculated [Count(CategoryID)]
  5. The above-formed sub-group is again filtered based on the condition given in the Having clause.
  6. Finally, from what we have the sorting is performed. Here, the order by clause will do the sorting based on the sub-group count of valid productId.

Note: The above examples are formed using the NorthWnd Sample database. The SQL Script for NorthWnd is attached.

No comments:

Post a Comment

Leave your comment(s) here.

Like this site? Tell it to your Friend :)