SQL for beginners: ORDER BY keyword

Summary

This video tutorial from D Beaver's YouTube channel teaches how to use the SQL 'ORDER BY' keyword to sort result sets. It covers sorting by ascending or descending order and sorting by different data types such as numbers, strings, and dates. The tutorial includes examples of sorting by numerical values and billing addresses, applying conditions for specific countries, and sorting by multiple columns. The video also demonstrates how to preserve sort order using SQL queries and briefly explains using D Beaver's data editor for sorting operations.

Takeaways

• 📊 SQL 'ORDER BY' is used to sort the result set of a query by specified columns.
• 📈 By default, 'ORDER BY' sorts data in ascending order (ASC). To sort in descending order, use the 'DESC' keyword.
• 🖥️ Sorting can be applied to numeric, string, and date columns in a database.
• 📝 Example query for sorting numerically: 'SELECT * FROM invoice ORDER BY total DESC' to sort by the 'total' column in descending order.
• 🏷️ String data can also be sorted, such as sorting the 'billing address' column using 'ORDER BY billing address'.
• 🌎 You can filter data based on conditions while sorting, like getting only US and Canadian addresses: 'SELECT * FROM invoice WHERE billing_country = 'USA' OR billing_country = 'Canada' ORDER BY billing_address'.
• 🔢 When sorting strings, rows beginning with numbers are sorted first, and special characters are sorted before numbers.
• 🔗 Multiple columns can be sorted simultaneously by separating the columns with commas, e.g., 'ORDER BY total, billing_address'.
• 🛠️ DBeaver allows sorting of data not only through queries but also directly in the UI using the context menu or column headers.
• 📺 The video is part of a series, with more tutorials on SQL basics available by subscribing to the channel.

Q & A

What is the purpose of the 'ORDER BY' keyword in SQL?

The 'ORDER BY' keyword is used to sort the result set of a query in either ascending or descending order based on one or more columns.

How do you write a query to sort a table by a specific column?

To sort a table by a specific column, write the 'SELECT' query followed by the 'ORDER BY' keyword and specify the column name. For example, 'SELECT * FROM invoice ORDER BY total'.

What is the default sorting order when using 'ORDER BY'?

The default sorting order when using 'ORDER BY' is ascending order.

How can you change the sorting order to descending in an SQL query?

To change the sorting order to descending, add the 'DESC' keyword after specifying the column in the 'ORDER BY' clause. For example, 'SELECT * FROM invoice ORDER BY total DESC'.

Can the 'ORDER BY' clause be used with non-numeric columns like strings?

Yes, the 'ORDER BY' clause can be used to sort string data. For example, 'SELECT * FROM invoice ORDER BY billing_address'.

How does SQL handle sorting when there are rows with numbers and special characters in string data?

Rows that start with numbers are displayed first, sorted in ascending order. If there were rows that start with special characters, they would be shown before the rows with numbers or letters.

How can you filter results by a specific condition while also sorting the data?

You can use the 'WHERE' clause to filter data and 'ORDER BY' to sort it. For example, to filter invoices from the US and Canada and sort by billing address, use: 'SELECT * FROM invoice WHERE billing_country = 'USA' OR billing_country = 'Canada' ORDER BY billing_address'.

Is it possible to sort a result set by multiple columns in SQL? How?

Yes, it is possible to sort by multiple columns. Simply list the column names separated by a comma. For example, 'SELECT * FROM invoice ORDER BY total, billing_address' sorts by total first, then by billing address.

What happens if you don't include an 'ORDER BY' clause in your query?

If you don't include an 'ORDER BY' clause, the result set will not have a specific order, and rows may appear in a different order each time the query is executed.

How can you apply sorting in DBeaver without adding 'ORDER BY' to the query?

In DBeaver, you can use the context menu or special icons in the column header to sort the result set without modifying the query. However, this sorting will not be preserved when you rerun the query.