SQL: SELECT Functions (Top, Count, Distinct, Max, Min)

On By Ben Larson Ph.D.In MS SQL Server

I introduced you to the SELECT statement in SQL in my last SQL posting: SQL: SELECT Statement

Now I am going to show you some tricks that will provide you with more functionality in your Select statements.

**note, in the examples below, I am working with the HumanResources.Employee table from the AdventureWorks2012 data set. If you want to follow along with the exercises, go to my previous SQL posting: SQL: SELECT Statement where I show you how to download and attach the AdventureWorks2012 database.

Top

Top allows you to limit the number of rows that are returned. (note Top is a T-SQL (Microsoft’s version of SQL command. MySQL and Oracle use the term Limit).

Select Top 10 *
from HumanResources.Employee
go

Using Top 10 we are querying only the first 100 records from the Employee table. This is often useful when you are exploring a database and just want to see what is in a table. Using Select * in some larger tables without using the Top command to limit the amount of output can result in a query that could take minutes to hours to complete.

Note that the query above returns the first 10 records based usually on the order records were entered. If you want to top 10 newest employees you would need to add an Order By command.

Select Top 10 *
from HumandResources.Employee
order by HireDate desc
go

The order by command sorts the results by the dates in the column HireDate in descending order (desc) most recent to earliest. If I wanted to go the other way, I would use the command: order by HireDate asc – ascending(asc)

Count()

If yon want a count of the number of records in a table, SQL has a Count() function. The syntax is below.

Select Count(*)
from HumandResources.Employee
go

This returns the number of records in the table. If you wanted to know how many male employees are in the table, you can add a Where clause (I will cover Where more in-depth in later lessons)

Select Count(*)
from HumandResources.Employee
where Gender like 'M'
go

Distinct()

Notice how there are repeated JobTitles in the table below. What if you wanted a list of job titles without any repeating elements.

sqlSelect14

In this case, you will use the Distinct() function. Placing the column you want in the parenthesis and the query will return a non repeating list.

Select Distinct(JobTitle)
from HumandResources.Employee
go

Now, what if you want to know how many distinct job titles exist in the company. Well, we can use the Count() function in conjunction with Distinct()

Select Count(Distinct(JobTitle))
from HumandResources.Employee
go

The query above will return a count (number) of the distinct job titles found in the table

Max() and Min()

Max() and Min() do pretty much what you would expect them to do. They return the maximum or minimum value in column.

Select Max(HireDate) as Max, Min(HireDate) as Min
from HumandResources.Employee
go

** “as” command in a Select statement gives a name to result columns in query

If you enjoyed this lesson, click LIKE below, or even better, leave me a COMMENT. 

Follow this link for more SQL content: SQL

Leave a Reply