Sorting Data with ORDER BY

The ORDER BY clause in MySQL allows you to sort the result set of a query based on one or more columns. You can specify the sorting order as ascending (ASC, the default) or descending (DESC).

Practical Examples and Use Cases

Example 1: Sorting Customers by Last Name

Imagine a customer table. You want to retrieve all customers, but sorted alphabetically by their last name.

SELECT * FROM customers ORDER BY last_name ASC;

Example 2: Sorting Products by Price (Descending)

You want to display a list of products, starting with the most expensive ones.

SELECT * FROM products ORDER BY price DESC;

Example 3: Sorting by Multiple Columns

Sort customers first by state, then by last name within each state.

SELECT * FROM customers ORDER BY state ASC, last_name ASC;

Real-World Examples

• E-commerce: Displaying product search results sorted by relevance, price, or rating.
• CRM: Sorting customer lists by activity date, lead score, or location.
• Inventory Management: Ordering stock lists by quantity, expiration date, or supplier.
• Reporting: Presenting sales data sorted by date, region, or product category.

Grouping Data with GROUP BY

The GROUP BY clause in MySQL is used to group rows that have the same values in specified columns into summary rows, like finding the number of customers in each city.

Practical Examples and Use Cases

Example 1: Counting Customers per City

Determine how many customers reside in each city.

SELECT city, COUNT(*) AS customer_count FROM customers GROUP BY city;

Example 2: Finding the Average Price per Product Category

Calculate the average price for each category of products.

SELECT category, AVG(price) AS average_price FROM products GROUP BY category;

Example 3: Using HAVING to Filter Grouped Results

Show only cities that have more than 5 customers.

SELECT city, COUNT(*) AS customer_count FROM customers GROUP BY city HAVING COUNT(*) > 5;

Real-World Examples

• E-commerce: Determining the best-selling products per category.
• Analytics: Calculating website traffic by source or device type.
• Finance: Summarizing transactions by account or date.
• Human Resources: Calculating the average salary per department.

Combining Sorting and Grouping

You can combine ORDER BY and GROUP BY to achieve more complex data analysis and presentation.

Practical Examples and Use Cases

Example 1: Customers per City, Sorted by Customer Count

Find the number of customers in each city and then sort the results in descending order based on the customer count.

SELECT city, COUNT(*) AS customer_count FROM customers GROUP BY city ORDER BY customer_count DESC;

Example 2: Average Price per Category, Sorted by Category Name

Calculate the average price for each category and sort the results alphabetically by category name.

SELECT category, AVG(price) AS average_price FROM products GROUP BY category ORDER BY category ASC;