Everything about SQL ORDER BY
The `ORDER BY` clause is used in SQL to sort the results of a query based on one or more columns. You can specify the sorting order as either ascending (ASC) or descending (DESC). The syntax of the `ORDER BY` statement is quite simple and intuitive.
SELECT column1, column2, …
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;In this article, i will discuss about a few concepts of ‘ORDER BY’ that are counterintuitive. Before going forward let us have a look at the execution order of SQL.
SQL Execution Order
Execution order shows a sequence of operations, in which each step produces an intermediate result set that is used by subsequent steps for further processing. For instance, the `GROUP BY` clause operates on the result set generated by the `WHERE` clause to perform grouping.
Schema for data used in this article
A SQL table in MySQL DB with customer details and contains columns CUSTOMER ID VARCHAR(30), CUSTOMERCITY VARCHAR(30), and CUSTOMERSTATE CHAR(2).
USE database;
CREATE TABLE IF NOT EXISTS CUSTOMER (
CUSTOMER_ID VARCHAR(30),
CUSTOMERCITY VARCHAR(30),
CUSTOMERSTATE CHAR(2)
);ORDER BY with DISTINCT vs without DISTINCT
The first exception to the above-mentioned execution order is the `ORDER BY` clause, which is executed after the `SELECT` statement (projection). However, if you attempt to use a column in the `ORDER BY` clause that is not included in the `SELECT` list, it will still execute. Try executing the below query.
SELECT CUSTOMERCITY
FROM CUSTOMER
ORDER BY CUSTOMERSTATE;
/*
RESPONSE : Result set with customer city sorted by customer state.
*/ This happens because the `ORDER BY` clause operates on the final result set (entire result set produced not just the projected ones) produced by the `SELECT` statement, and SQL requires that columns used in the `ORDER BY` clause must be part of the final result set.
However, if you attempt running the below query, the result will be a runtime error. The reason for this error is because the final result set will not contain columns that aren’t projected. Once the distinct / union operations are applied the final result set will be updated by only manipulating the resultant column set that are projected. Hence, the only columns available for order by will be the columns used in the projection.
SELECT DISTINCT CUSTOMERCITY
FROM CUSTOMER
ORDER BY CUSTOMERSTATE
/*
RESPONSE : Error Code: 3065. Expression #1 of ORDER BY clause is not in
SELECT list, references column 'salesorder_dm.customer_dim.customerState'
which is not in SELECT list; this is incompatible with DISTINCT
*/ORDER BY and NULLS
Different dialects of SQL follow a different approach when sorting the columns that contain nulls. MYSQL versions before 8.0.0 nulls always appeared first in the sort sequence, even if we use DESC. However, starting from version 8.0.0 nulls appear at the top in sort sequence if we use ORDER BY [column with nulls] ASC and at bottom if we use ORDER BY [column with nulls] DESC.
However, in Oracle DB the approach is contradictory. Nulls are treated as the highest possible values by default, if we use ORDER BY [column with nulls] ASC null appear at bottom of sort sequence, else if we use ORDER BY [column with nulls] DESC nulls appear at the top of sort sequence.
Usage of Aggregations / Expression with ORDER BY
The `ORDER BY` clause in SQL can be used to sort results based on any expression or arithmetic operation involving the columns in the result set. This functionality is consistent across all SQL dialects. Expressions can include case statements, arithmetic operations on other columns, or any combination of these. Indeed, the above syntax can be rewritten as
SELECT column1, column2, …
FROM table_name
ORDER BY expressions ASC|DESC;ORDER BY with characters or strings
Get Pradeep Gontla’s stories in your inbox
Join Medium for free to get updates from this writer.
Try answering the question before going forward : Are string comparisons case-sensitive in SQL?
Sorting operations on strings involve comparing the characters within the strings and arranging them according to specified criteria. Collation in SQL defines such criteria that helps in sorting the columns with char or varchar data type.
The character set (charset) and collation work together to define the content and comparison rules for a column. The charset specifies the set of characters or special symbols that can be used in the table.
Ex : `utf8mb4` charset allows the use of Unicode characters in a column.
Databases typically support multiple charsets. In MySQL versions 8.0.0 and later, the default charset is `utf8mb4`, which supports 4-byte characters. You can view the available charsets and choose one at the table, column, or string literal level as needed.
/*
Displays all available charsets.
*/
SHOW CHARACTER SET;
/*
Defining the charset at table level.
*/
Create table customers (customer_name varchar(40) not null) charset = latin1;
/*
Defining the charset at column level.
*/
CREATE TABLE `customers` (
`customer name` VARCHAR(255) CHARACTER SET latin1,
`customer_id` VARCHAR(255)
)String comparisons are governed by the collation associated with the charset. Collation is a set of rules used to compare and sort characters within that charset. To view the available collations for each charset and select a specific collation in MySQL, we can execute the below query for .
Show collation where charset = ‘charsetname’;
/*
RESPONSE : This will give you a set of collations with various suffixes.
*/
+--------+--------------------+
| Suffix | Meaning |
+--------+--------------------+
| _ai | Accent-insensitive |
| _as | Accent-sensitive |
| _ci | Case-insensitive |
| _cs | Case-sensitive |
| _ks | Kana-sensitive |
| _bin | Binary |
+--------+--------------------+To answer the above question, whether string comparisons are case sensitive is it depends on the collation we choose. To get a better understanding try running below code on MySQL server the response will be 1 indicating that both ‘MEDIUM’ and ‘medium’ are same strings.
SELECT "MEDIUM" COLLATE utf8mb4_0900_ai_ci = "medium" COLLATE utf8mb4_0900_ai_ci;
-- Response will be 1Just like charset, collate can be provided at table level or column level. Additionally we can use Alter statement to update collate once the table is created.
-- Defining charset and collate at table level
Create table customers (customer_name varchar(40) not null)
CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
-- Defining charset and collate at column level.
CREATE TABLE `customers` (
`customer name` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
`customer_id` VARCHAR(255)
);References
[1] Murach’s MYSQL — Joel Murach
[2] SQL Execution Order
[3] Relation between SQL DISTINCT and ORDER BY
[4] Working with Nulls
https://dev.mysql.com/doc/refman/8.4/en/working-with-null.html
[5] SQL charsets and collation
