By default the result set that gets returned to us from a query statement is sorted randomly. Meaning there is no default sort order in SSMS, which is not very useful. For a result set to be meaningful we might need to sort it in some way.
Luckily, there is a function in SSMS that allows us to perform this type of functionality.
Sorting
To sort our query statement result set we use the following syntax:
Select [Column Name | *]
Where [Column Name] Like [criteria]
AND | OR [Column Name] [operator] [criteria]
Order By [Column Name] (asc/desc), [Column Name] (asc/desc),...
We can either ORDER BY our specified column in asc ascending order or we can sort our specified column in desc descending order. We can also use as many columns as we prefer and sort them in whatever order we prefer.
The Order By clause will first sort the order of the first column with the method we specify, then from that result it will order the next column, in the order that we specified for that particular column and so on and so forth...
We can experiment with the Order By clause inside of SSMS.
Remember to make sure that you are connected to the correct database, in this case AdventureWorks.
We will be using the Purchasing.PurchaseOrderDetail table in this discussion.
Up until this point we have only been typing out the table names that we want to run queries against. However there is an easier method of doing this. We can choose the table on the left panel and drag it over to the right panel on the query window.
We can see all the columns and records available to us in the Purchasing.PurchaseOrderDetail table. We can now run an Order By clause. Remember if we were running a Where clause, then it would come before the Order By clause.
We are going to Order By OrderQTY (order quantity), we will first use asc and then use desc.
When we use an Order By clause and choose a column name, we do not need to specify asc if we want the column to be in ascending order. SSMS will order it in ascending order by default. But if you want to be declarative in your database then you would still type asc.
When we add additional columns and sort through them as well, SSMS will first sort the first specified column and then the next column in the query statement.
In this case it will first sort OrderQTY in desc, then use that order structure to sort the order for the UnitPrice.
Select Unique records
We have discussed the SELECT statement that return all the rows that match our criteria. In some cases we might have duplicate records, and maybe we only want to return unique records from the database. Going forward we will be discussing the SQL query statement we can use to return unique records only.
Our SQL query statement is exactly the same as before, the only difference will be the DISTINCT keyword which will come before our Column Name in our SELECT statement.
The syntax will be as follows:
Select Distinct [Column Names | *]
Where [Column Name] Like [criteria]
AND | OR [Column Name] [operator] [criteria]
Order By [Column Name] (asc/desc), [Column Name] (asc/desc),...
The DISTINCT keyword instructs SQL to only return records that are distinct, which means only unique records are returned.
We can see this in practice inside SSMS.
This time we will be selecting from the Person.Person table. We will also be choosing to select from the firstName column instead of all the columns.
As you can see we have duplicate firstName values in our database. In order to have a result set returned with only unique values we need to insert the Distinct keyword.
We can check if all the names are unique by sorting the record by firstName in ascending order.
Data Model
We have covered a fair amount of query statements in SQL, however we need to discuss some key concepts of a relational database to fully understand concepts we will be discussing in future articles.
Tak a close look at the database models below.
Customers
| Customer ID | Customer Name | Customer Address |
|---|---|---|
| C1 | Fred | ... |
| C2 | Bert | ... |
| C3 | Jane | ... |
Products
| Product ID | Product Name | Description |
|---|---|---|
| P1 | Shirt | ... |
| P2 | Tie | ... |
| P3 | Collar | ... |
Orders
| Order ID | Customer ID | Product ID | Quantity |
|---|---|---|---|
| 1000 | C1 | P1 | 1 |
| 1001 | C2 | P1 | 3 |
| 1002 | C1 | P3 | 1 |
| 1003 | C1 | P3 | 2 |
| 1004 | C2 | P2 | 4 |
| 1005 | C1 | P2 | 2 |
| 1006 | C3 | P3 | 1 |
Primary Key
You design a relational database by creating a data model. The model below shows the structure of the entities from the previous example. In this diagram, the columns marked PK are the Primary Key for the table. The primary key indicates the column (or combination of columns) that uniquely identify each row. Every table should have a primary key.
The diagram also shows the relationships between the tables. The lines connecting the tables indicate the type of relationship. In this case, the relationship from customers to orders is 1-to-many (one customer can place many orders, but each order is for a single customer). Similarly, the relationship between orders and products is many-to-1 (several orders might be for the same product).
Foreign Keys
The columns marked FK are Foreign Key columns. They reference, or link to, the primary key of another table, and are used to maintain the relationships between tables. A foreign key also helps to identify and prevent anomalies, such as orders for customers that don't exist in the Customers table. In the model below, the Customer ID and Product ID columns in the Orders table link to the customer that placed the order and the product that was ordered:
Understanding the way we structure our database, how we model our tables and how they are related to each other is an integral step towards SQL mastery.
Top comments (0)