Sorting data

1. Sorting data

Welcome to this SQL with AI course, where we'll discover new ways to explore data, analyze it, and uncover insights.

2. Explore and analyze data

We'll build on our foundational skills, learning to sort, group, and summarize data, then sharpen our queries with filtering to focus on what matters most. Finally, we'll create custom categories that make our analysis clearer and easier to share. And we'll do it all through prompting.

3. Film database

We'll practice with a film database containing three tables: films, people, and reviews. Our AI assistant knows these tables and fields, but we'll learn how to guide it and verify every result since prompts and queries can vary. In real-world scenarios, always get permission before sharing context with AI. Let's get started with sorting.

4. Sorting

Imagine a messy bookshelf. To find the book, "Matilda," we'd have to check each one until we spotted it, like scrolling through rows of data. Sorting arranges data in order, making the search quicker. It's often the first step analysts take to spot patterns.

5. Ascending order

Sorting works on any field, but let's begin by alphabetizing film titles with the prompt "Show all film titles alphabetically". The AI assistant adds an ORDER BY clause to sort titles from A to Z. Special characters and numbers appear first, followed by uppercase then lowercase letters. In PostgreSQL, ascending order is the default, indicated by the optional ASC keyword. Results usually appear this way if we don't specify an order in the prompt.

6. Descending order

We can also sort values in the opposite direction, called descending order. For text, the prompt "Show all film titles in descending order" sorts results from Z to A, with lowercase before uppercase. Characters like Æ may appear at the top if they're stored after lowercase z. The query also now includes a DESC keyword to indicate the sorting direction.

7. Specify the order

There are several ways to prompt the sorting strategy we want. For ascending order, consider "oldest first", "alphabetical", "from A to Z", or "chronological". For descending order, try "newest first", "from Z to A", or "counting down".

8. Vague prompts

We can sort on a field that isn't displayed. Here is an example showing titles sorted by release year. However, if the year field isn't displayed, we can't confirm that the sort worked.

9. Being specific

For clarity, it's best to include the sorted field, like with the prompt "Show titles and release years, sorted by release year".

10. Sorting multiple fields

The ORDER BY clause can also sort by multiple fields. It starts with the first listed field, then applies the next, and so on. Think of this like a tie-breaker. Adding another resolves the tie if the first field doesn't fully determine the order. For example, using an awards dataset, sorting by Oscar wins only may produce ties. By adding another award as a secondary sort, we can reveal a clear winner.

11. Different directions

Finally, we can sort on multiple fields, and choose the direction for each. For example, "Show film titles and release years sorted by year descending, then title ascending" lists the newest release years first, then alphabetizes titles. We can verify this by checking the years decrease, and the titles are alphabetized within each year. Notice that the query includes keywords to clarify the different orders, and missing values appear first in descending order. We'll cover filtering missing values later in the course.

12. Let's practice!

For now, let's practice sorting.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.