# ORDER BY
(opens new window) , below is an example to return the Id, FName and LName columns in (ascending) LName order:
| Id | FName | LName |
|---|---|---|
| 2 | John | Johnson |
| 1 | James | Smith |
| 4 | Johnathon | Smith |
| 3 | Michael | Williams |
To sort in descending order add the DESC keyword after the field parameter, e.g. the same query in LName descending order is:
# ORDER BY multiple fields
Multiple fields can be specified for the ORDER BY clause, in either ASCending or DESCending order.
(opens new window) table, we can return a query that sorts by SaleDate in ascending order, and Quantity in descending order.
Note that the ASC keyword is optional, and results are sorted in ascending order of a given field by default.
# ORDER BY with complex logic
If we want to order the data differently for per group, we can add a CASE syntax to the ORDER BY . In this example, we want to order employees from Department 1 by last name and employees from Department 2 by salary.
| Id | FName | LName | PhoneNumber | ManagerId | DepartmentId | Salary | HireDate |
|---|---|---|---|---|---|---|---|
| 1 | James | Smith | 1234567890 | NULL | 1 | 1000 | 01-01-2002 |
| 2 | John | Johnson | 2468101214 | 1 | 1 | 400 | 23-03-2005 |
| 3 | Michael | Williams | 1357911131 | 1 | 2 | 600 | 12-05-2009 |
| 4 | Johnathon | Smith | 1212121212 | 2 | 1 | 500 | 24-07-2016 |
| 5 | Sam | Saxon | 1372141312 | 2 | 2 | 400 | 25-03-2015 |
# Custom Ordering
If you want to order by a column using something other than alphabetical/numeric ordering, you can use case to specify the order you want.
order by Group returns:
| Group | Count |
|---|---|
| Not Retired | 6 |
| Retired | 4 |
| Total | 10 |
order by case group when ‘Total’ then 1 when ‘Retired’ then 2 else 3 end returns:
| Group | Count |
|---|---|
| Total | 10 |
| Retired | 4 |
| Not Retired | 6 |
# Remarks
The purpose of the ORDER BY clause is to sort the data returned by a query.
It’s important to note that the order of rows returned by a query is undefined unless there is an ORDER BY clause.
SQL оператор ORDER BY
В этом учебном материале вы узнаете, как использовать SQL оператор ORDER BY с синтаксисом и примерами.
Описание
SQL оператор ORDER BY используется для сортировки записей в наборе результатов запроса SELECT.
Синтаксис
Синтаксис для оператора ORDER BY в SQL.
Параметры или аргумент
Примечание
- Если модификатор ASC или DESC не указан в предложении ORDER BY, результаты будут отсортированы по expressions в порядке возрастания. Это эквивалентно ORDER BY expressions ASC
Пример — сортировка результатов по возрастанию
Чтобы отсортировать результаты в порядке возрастания, вы можете указать атрибут ASC. Если после поля в предложении ORDER BY не указано значение (ASC или DESC), порядок сортировки по умолчанию будет соответствовать возрастающему. Давайте рассмотрим это дальше.
В этом примере у нас есть таблица customers со следующими данными:
How to Use the SQL ORDER BY Clause
If you want to refine your output when coding, the SQL ORDER BY clause is an awesome way to go about it. However, in order to fully understand how to work with it, you should check out what operators we will be using first.
Let’s see what happens when we retrieve all records from the “ employees” table. As shown in the picture below, the list we get is automatically ordered based on employee numbers.
Assume your boss has just asked you to order the people by first name, instead of by employee number. So, how can you do that?
When to Use the SQL ORDER BY clause
Adding “ ORDER BY first name” at the end of this query will provide the desired outcome.
As you can see in the picture above, the entire list was reorganized in alphabetical order, according to the field containing employee names.
Specifying the Order
One of two specific reserved words can be attached at the end of the ORDER BY clause.
Ascending Order
The first one is ASC, abbreviated from “ascending”, requiring the output to be sorted by the values in the designated field in ascending order. If this keyword is not attached to the statement at the end, SQL will implicitly understand you want things ordered precisely in ascending order anyway.
Therefore, if we add ASC at the end and re-run the query, we will obtain the same output.
Descending Order
The alternative is to use DESC, which is abbreviated from “descending”. Hence, if you would like your results plotted in reverse order, DESC is the keyword to add at the end of the ORDER BY clause.
Let’s check if this places the names starting with Z first.
Working with Numbers
Now, the SQL ORDER BY clause does not only work for columns containing string values. It can handle numbers as well!
Let’s sort the list by employee number in descending order. Our query will look like this:
This way, we start with a larger employee number that decreases as we scroll down!
Ordering by More than One Field
Let’s see another interesting feature of this clause. You can order your results by more than one field. For instance, we can order employees by first and last name.
To make the comparison, let’s re-run the query where we sorted our output by first name in ascending order.
Now, look at the order of last names.
We can sort employees sharing the same first name according to their last name. To do this, we must simply type:
By writing this “, last_name “, we designated the second column of interest.
Now all people with the same first name are ordered by their surname.
The Benefits of Ordering Data
To sum up, the SQL ORDER BY clause is not mandatory. Rather, it is a helpful tool. Whenever you need to sort your data in ascending or descending order, you can use it.
Otherwise, there’s yet another extremely powerful tool that will allow you to retrieve more meaningful data by avoiding repetition — The GROUP BY clause.
SQL ORDER BY Keyword
The ORDER BY command is used to sort the result set in ascending or descending order.
The ORDER BY command sorts the result set in ascending order by default. To sort the records in descending order, use the DESC keyword.
The following SQL statement selects all the columns from the «Customers» table, sorted by the "CustomerName" column:
Example
The ASC command is used to sort the data returned in ascending order.
The following SQL statement selects all the columns from the «Customers» table, sorted by the "CustomerName" column:
Example
The DESC command is used to sort the data returned in descending order.
The following SQL statement selects all the columns from the «Customers» table, sorted descending by the "CustomerName" column: