Learn SQL: Microsoft SQL Server - Episode 4: Sorting Results
Tags: sql, database, beginners, datascience
Published: 2021-10-28 04:13:24

![Goran-come-and-greet-everyone](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/sejgupjy6c2b5mpz9ooa.gif)

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 <abbr title="SQL Server Management Studio">SSMS</abbr>, 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 <abbr title="SQL Server Management Studio">SSMS</abbr> that allows us to perform this type of functionality.

![skid-10](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bknt8y3h7n37t21o72rz.png)

###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...

![skid-9](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/h1h2iq6yg9h4zia1gkao.gif)

We can experiment with the **Order By** clause inside of <abbr title="SQL Server Management Studio">SSMS</abbr>.

>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.

![select-all-query-statement](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/khy2k787hw6apr2a19xz.gif)

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.

![skid-2](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ul6ma9chdnsash0g5vh4.gif)

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. <abbr title="SQL Server Management Studio">SSMS</abbr> will order it in ascending order by default. But if you want to be declarative in your database then you would still type asc.

![order-by-asc-and-desc](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dxngk2mdhcdxetavgso5.gif)

![skid-9](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/h1h2iq6yg9h4zia1gkao.gif)

When we add additional columns and sort through them as well, <abbr title="SQL Server Management Studio">SSMS</abbr> will first sort the first specified column and then the next column in the query statement. 

![order-by-asc-and-desc-1](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/eh9o8nw4wb5lfk2414pc.gif)

In this case it will first sort _OrderQTY_ in **desc**, then use that order structure to sort the order for the _UnitPrice_. 

![skid-10](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bknt8y3h7n37t21o72rz.png)

####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 <u>unique records only</u>.

![skid-2](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ul6ma9chdnsash0g5vh4.gif)

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.

![skid-9](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/h1h2iq6yg9h4zia1gkao.gif)

We can see this in practice inside <abbr title="SQL Server Management Studio">SSMS</abbr>.

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.

![select-firstname](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ld5ca7jr5nzs24trvqra.gif)

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.

![select-distinct-query](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4ppoxuvcwxgkuwzxvenp.gif)

We can check if all the names are unique by sorting the record by _firstName_ in ascending order.

![skid-marks-1](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pkzftzeavjt75ougfx95.png)

###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

![skid-marks-1](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pkzftzeavjt75ougfx95.png)
####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.
![skid-2](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ul6ma9chdnsash0g5vh4.gif)
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).
![skid-2](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ul6ma9chdnsash0g5vh4.gif)
####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:

![sql-data-model](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/o73rdbwucocgc452orf6.png)

![skid-10](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bknt8y3h7n37t21o72rz.png)

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. 

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kpf9t610pxwayrvpspgk.gif)