Organizing Your Data: Mastering the ORDER BY Clause, Aliasing, and the DISTINCT Keyword in SQL

https://www.digitalocean.com/community/tutorials/how-to-use-groupby-and-orderby-in-sql

We talked about how to filter data using the WHERE syntax and filtering it down to more specific details using the Boolean operator. We also talked about how to delete or update rows in your tables. Now we will talk about how we can organize our data through the use of ORDER BY and how we rename and structure our columns and tables through the use of Aliasing. Finally we will discuss how we can filter out data and get unique values through the use of the DISTINCT keyword. Lets get started!

ORDER BY

Purpose: The ORDER BY clause helps sort query results in ascending or descending order. Ascending being the default if we don’t specify anything.

Syntax and Examples

Basic Syntax:

SELECT column1, column2 FROM table
ORDER BY column1, column2 ASC|DESC;

Example: Ordering by Salary (Descending):

SELECT * FROM employee
ORDER BY Salary DESC;

This will give us the highest to lowest salary.

Example: Ordering by Name (Descending): We can also Order by Text.

SELECT * FROM employee
ORDER BY name DESC;

This will give us names from Z to A.

Default Ordering (Ascending): If DESC is removed, it will order names from A to Z.

Using ORDER BY in SQL ( Click to enlarge GIF )

ALIASING

Purpose: Aliasing can help rename columns and tables to avoid confusion and prevent errors, especially when combining tables with the same column names.

Syntax and Examples

Aliasing Columns:

SELECT column AS alias
FROM table;

Example:

SELECT customer_id AS cid
FROM customers;

Aliasing Tables:

SELECT column
FROM table AS alias;

Example:

SELECT c.customer_id
FROM customers AS c;

Using Table and Column Aliases Together:

SELECT c.customer_id AS cid
FROM customers AS c;

Note: Aliasing does not change the actual table name or column names in the database.

Using the AS Keyword in SQL ( Click to enlarge GIF )

DISTINCT

Purpose of DISTINCT: The word distinct means unique so that means in the case of SQL that the DISTINCT keyword retrieves unique records, eliminating duplicates.

Syntax and Examples

Basic Syntax:

SELECT DISTINCT column1 FROM table;

Example: Retrieving Unique Customer First Names: If we want to see all the customers who came to our store but the issue is some customers come pretty often and I just want to pull exactly each customers name once in the first name column. So we write it as so,

SELECT DISTINCT first_name FROM customers;

This will show each customer’s first name only once, avoiding duplicates.

Caution with DISTINCT: Just because we see duplicate rows doesn’t mean we always use DISTINCT. Using DISTINCT can be costly in terms of time complexity. In that case ensure that the use of DISTINCT is necessary by investigating the root cause of duplicate rows.

Using the DISTINCT keyword in SQL ( Click to enlarge GIF )

Overall, we learned how to use ORDER BY to organize data in ways we couldn't do before. This keyword can help us figure out the top or bottom of whatever were trying to look at like top performing employees etc. Then we learned how to rename columns and tables so that our data becomes more structured and clear by using the AS keyword.

Helping us prevent errors from same column names across different tables as an example. Finally we learned how to use the keyword DISTINCT to filter out values in columns that appear more than once. While useful it isn’t always ideal just to use to filter out duplicates. You should stray away from using as a fix and more of as a tool for analysis. Make sure to practice these keywords on your own and as always have a Good morning, Good evening and Good night.