Examples of Formatting and Sorting Data in Real-World Tasks

Sorting and formatting data are super important skills that let you prep readable reports, make data analysis easier, and improve user experience. You'll need this stuff when building analytics reports, prepping data for export, and just in your day-to-day work with databases. In real life, you'll often run into tasks where you gotta make data look nice, remove duplicate records, and sort info so it's easy to read. That's exactly what we're gonna do today!

Example 1: Creating a List of Unique Customers with Combined First and Last Name, Sorted by Last Name

We've got a customers table that stores info about customers:

Our goal:

1. Combine first_name and last_name into a single column called full_name.
2. Get only unique customers.
3. Sort the list by last name (last_name).

SQL query

SELECT DISTINCT
    CONCAT(first_name, ' ', last_name) AS full_name,
    city
FROM customers
ORDER BY full_name;

Important: with SELECT DISTINCT the ORDER BY can only use expressions from the SELECT list (or their aliases). The query ORDER BY last_name here would fail with for SELECT DISTINCT, ORDER BY expressions must appear in select list — that's why we sort by the already-combined full_name.

Notice that the duplicate Alex Lin records were removed thanks to DISTINCT, and the whole list is sorted by last name in alphabetical order.

Example 2: Formatting Order Data and Sorting It

The orders table stores info about orders:

Our goal:

1. Create a formatted_order_date column where the order date is in DD-MM-YYYY format.
2. Remove duplicate customer and date records (keep only unique customer_name and order_date combos).
3. Sort orders by date in descending order.
4. SQL query

SELECT DISTINCT
customer_name,
TO_CHAR(order_date, 'DD-MM-YYYY') AS formatted_order_date,
total_amount
FROM orders
ORDER BY formatted_order_date DESC;

Heads up: we sort by the alias formatted_order_date, not by order_date itself — otherwise with DISTINCT we'd get the error "ORDER BY expressions must appear in select list". Note that sorting goes by the DD-MM-YYYY string — lexicographically, not as an actual date.

Result:

See how with the TO_CHAR() function we changed the date format to DD-MM-YYYY, and with DISTINCT we got rid of duplicate records.

Example 3: Getting Unique "First Name + Last Name" Combos for Students and Sorting by Last Name and Birth Date

The students table has info about students:

Our goal:

• Combine first and last name into a single column full_name.
• Get unique "first name + last name" combos.
• Sort students by last name, then by birth date.

SELECT DISTINCT
    CONCAT(first_name, ' ', last_name) AS full_name,
    birth_date
FROM students
ORDER BY full_name, birth_date;

Result:

Heads up: two identical records for student "Alex Lin" were merged into one row, and sorting was done first by last name, then by birth date.

Practice Task

Use what you learned today to solve the following task:

Task: You have a products table with the following data:

1. Create a formatted_product column where product_name is combined with category using a dash, for example: Phone - Electronics.
2. Remove duplicate product_name and category combos.
3. Sort products by category, then by price (from cheapest to most expensive).

Here's a query structure to help you out:

SELECT DISTINCT
    CONCAT(product_name, ' - ', category) AS formatted_product,
    price
FROM products
ORDER BY formatted_product, price ASC;

Try to imagine what the result of this query would look like!

Using CONCAT(), DISTINCT, and ORDER BY functions lets you get super readable and structured data, which is absolutely critical in real projects and tasks. Make sure you get how to combine them by practicing with examples!

2

Task

SQL SELF, level 6, lesson 3

Locked

Formatting and Sorting the List of Students

Formatting and Sorting the List of Students

Open

2

Task

SQL SELF, level 6, lesson 3

Locked

Unique Orders with Customer Formatting

Unique Orders with Customer Formatting

Open