DB --> LEC 7 --> Topics --> 42 --> 49

Let’s dive into each of these SQL concepts with detailed explanations, focusing on their usage in MySQL. I’ll provide clear examples and highlight key nuances to ensure a thorough understanding.

42. SQL ORDER BY Clause

The ORDER BY clause in SQL is used to sort the result set of a query based on one or more columns. By default, sorting is done in ascending order (ASC), but you can explicitly specify ascending or descending order.

Syntax:

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

Key Points:

ORDER BY is typically the last clause in a SELECT statement (before LIMIT or OFFSET).
You can sort by columns not included in the SELECT list (though this is less common).
MySQL allows sorting by column names, aliases, or column positions (e.g., ORDER BY 1 refers to the first column in the SELECT list).

Example:

SELECT first_name, last_name, salary
FROM employees
ORDER BY salary;

This sorts the result set by the salary column in ascending order.

43. ORDER BY ASC (Ascending Order)

The ASC keyword explicitly specifies that sorting should be in ascending order (smallest to largest for numbers, A to Z for strings). Since ASC is the default in MySQL, it’s optional.

Syntax:

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 ASC;

Example:

SELECT product_name, price
FROM products
ORDER BY price ASC;

This lists products from the lowest price to the highest.

Key Points:

For numbers, ascending means smaller values appear first (e.g., 1, 2, 3).
For strings, ascending follows alphabetical order (e.g., Apple, Banana, Cherry).
For dates, ascending means earlier dates come first.

44. ORDER BY DESC (Descending Order)

The DESC keyword specifies sorting in descending order (largest to smallest for numbers, Z to A for strings).

Syntax:

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 DESC;

Example:

SELECT product_name, price
FROM products
ORDER BY price DESC;

This lists products from the highest price to the lowest.

Key Points:

Use DESC to reverse the default ascending order.
You can mix ASC and DESC when sorting multiple columns (see below).
Sorting is case-sensitive for strings in MySQL, depending on the collation (e.g., utf8mb4_general_ci is case-insensitive).

45. ORDER BY With Multiple Columns

You can sort by multiple columns by listing them in the ORDER BY clause. MySQL sorts by the first column, then by the second column for rows with equal values in the first column, and so on.

Syntax:

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

Example:

SELECT department, salary, first_name
FROM employees
ORDER BY department ASC, salary DESC;

This sorts employees first by department (A to Z), then within each department, by salary (highest to lowest).

Output (Sample):

department	salary	first_name
HR	80000	Alice
HR	60000	Bob
IT	90000	Charlie
IT	70000	Dave

Key Points:

The order of columns in ORDER BY matters.
You can use different sorting directions (ASC or DESC) for each column.
MySQL evaluates columns left to right in the ORDER BY clause.

46. ORDER BY With WHERE

The ORDER BY clause can be combined with a WHERE clause to filter rows before sorting. The WHERE clause filters the rows, and ORDER BY sorts the filtered result set.

Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column1 [ASC|DESC];

Example:

SELECT first_name, salary
FROM employees
WHERE department = 'IT'
ORDER BY salary DESC;

This retrieves employees in the IT department and sorts them by salary in descending order.

Output (Sample):

first_name	salary
Charlie	90000
Dave	70000

Key Points:

The WHERE clause is evaluated before ORDER BY.
You can combine WHERE with multiple ORDER BY columns.
Ensure the WHERE condition is efficient (e.g., use indexes) to avoid performance issues.

47. SQL GROUP BY

The GROUP BY clause groups rows with identical values in specified columns into summary rows. It’s typically used with aggregate functions (e.g., COUNT, SUM, AVG, MAX, MIN) to compute summaries for each group.

Syntax:

SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1;

Example:

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

This counts the number of employees

department	employee_count
HR	3
IT	4
Sales	2

Key Points:

Columns in the SELECT list must either be in the GROUP BY clause or used in an aggregate function.
In MySQL, you can sometimes select non-aggregated columns not in GROUP BY (non-standard behavior), but this can lead to unpredictable results unless ONLY_FULL_GROUP_BY is disabled.
GROUP BY is evaluated after WHERE but before ORDER BY.

48. SQL HAVING Clause

The HAVING clause filters groups created by GROUP BY based on conditions involving aggregate functions. It’s like WHERE but applies to grouped data.

Syntax:

SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1
HAVING condition;

Example:

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 70000;

This returns departments where the average salary exceeds 70000.

Output (Sample):

department	avg_salary
IT	75000

Key Points:

HAVING can only reference aggregated columns or columns in the GROUP BY clause.
It’s evaluated after GROUP BY but before ORDER BY.
You can use multiple conditions in HAVING with AND, OR, etc.

49. SQL HAVING vs. WHERE

While both WHERE and HAVING filter data, they serve different purposes and are applied at different stages of query execution.

Key Differences:

Aspect	WHERE	HAVING
Purpose	Filters individual rows before grouping	Filters groups after `GROUP BY`
Applied To	Raw rows in the table	Aggregated groups
Aggregate Functions	Cannot use (e.g., `COUNT`, `AVG`)	Can use (e.g., `COUNT`, `AVG`)
Execution Order	Before `GROUP BY`	After `GROUP BY`
Columns	Can reference any column in the table	Limited to `GROUP BY` columns or aggregates

Example (Combining WHERE and HAVING):

SELECT department, COUNT(*) AS employee_count
FROM employees
WHERE salary > 50000
GROUP BY department
HAVING COUNT(*) > 2
ORDER BY employee_count DESC;

WHERE salary > 50000: Filters out employees with salaries ≤ 50000.
GROUP BY department: Groups the remaining rows by department.
HAVING COUNT(*) > 2: Keeps only departments with more than 2 employees.
ORDER BY employee_count DESC: Sorts the result by the count in descending order.

Output (Sample):

department	employee_count
IT	4
HR	3

Key Points:

Use WHERE to reduce the number of rows before grouping for better performance.
Use HAVING when filtering based on aggregate results (e.g., SUM, COUNT).
In MySQL, HAVING can sometimes reference non-aggregated columns not in GROUP BY (non-standard), but this is discouraged for portability.

Database SQL

FOX Academy

DB --> LEC 7 --> Topics --> 42 --> 49

42. SQL ORDER BY Clause

43. ORDER BY ASC (Ascending Order)

44. ORDER BY DESC (Descending Order)

45. ORDER BY With Multiple Columns

46. ORDER BY With WHERE

47. SQL GROUP BY

48. SQL HAVING Clause

49. SQL HAVING vs. WHERE

You may like these posts

Also Like

DB --> LEC 7 --> Topics --> 42 --> 49

42. SQL ORDER BY Clause

43. ORDER BY ASC (Ascending Order)

44. ORDER BY DESC (Descending Order)

45. ORDER BY With Multiple Columns

46. ORDER BY With WHERE

47. SQL GROUP BY

48. SQL HAVING Clause

49. SQL HAVING vs. WHERE

You may like these posts