Sql server order by
Содержание:
- SQL ORDER BY with expressions
- Таблица базы данных
- Внешнее соединение
- SQL Учебник
- SQL Учебник
- Example — Sorting Results by relative position
- MySQL ORDER BY Descending
- Database Tables
- SQL Учебник
- Introduction to Oracle ORDER BY clause
- SQL Справочник
- MySQL ORDER BY examples
- Sort Operations and Clustered Index
- SQL Справочник
- Example: Sort by ordinal positions of columns
- Oracle ORDER BY clause examples
- Sorting results in ascending order
- SQL Учебник
SQL ORDER BY with expressions
The clause can also accept expressions. For example, you can use the string function to construct full names of employees, and then sort the result set by the full name as the following query:
The column alias is used for formatting the output of the result set. You can use the column alias in the ORDER BY clause rather than expression. The following query produces the same output:
SQL ORDER BY with positional number
The positional number is the position of the column in the clause. The position number starts with 1, 2, 3, etc. SQL allows you to use these positional numbers rather than columns or expressions to sort the result set.
The following statement sorts the employees by hired date in descending order to find the most junior employees in the company:
SQL sorts the result set by column, which has positional number 3.
The positional number that refers to a specific column is changed when you change the columns in the clause. This may lead to an unexpected result if you forget to change the positional number. Therefore, it is not recommended to use the positional number in the clause. You only use it if you have no choice.
In this tutorial, you’ve learned how to use the clause to sort result sets returned by a statement.
Таблица базы данных
База данных чаще всего содержит одну или несколько таблиц.
Каждая таблица идентифицируется по имени (например, «клиенты» или «заказы»).
Таблицы содержат записи (строки) с данными.
В этом уроке мы будем использовать хорошо известный образец базы данных Northwind (входит в MS Access и MS SQL Server).
Ниже приведен выбор из таблицы «клиенты»:
| CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
|---|---|---|---|---|---|---|
| 1 | Alfreds Futterkiste | Maria Anders | Obere Str. 57 | Berlin | 12209 | Germany |
| 2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
| 3 | Antonio Moreno Taquería | Antonio Moreno | Mataderos 2312 | México D.F. | 05023 | Mexico |
| 4 | Around the Horn | Thomas Hardy | 120 Hanover Sq. | London | WA1 1DP | UK |
| 5 | Berglunds snabbköp | Christina Berglund | Berguvsvägen 8 | Luleå | S-958 22 | Sweden |
Приведенная выше таблица содержит пять записей (по одной для каждого клиента)
и семь столбцов (CustomerID, CustomerName, ContactName, Address, City, PostalCode и Country).
Внешнее соединение
В предшествующих примерах естественного соединения, результирующий набор содержал только те строки с одной таблицы, для которых имелись соответствующие строки в другой таблице. Но иногда кроме совпадающих строк бывает необходимым извлечь из одной или обеих таблиц строки без совпадений. Такая операция называется внешним соединением (outer join).
В примере ниже показана выборка всей информации для сотрудников, которые проживают и работают в одном и том же городе. Здесь используется таблица EmployeeEnh, которую мы создали в статье «Инструкция SELECT: расширенные возможности» при обсуждении оператора UNION.
Результат выполнения этого запроса:
В этом примере получение требуемых строк осуществляется посредством естественного соединения. Если бы в этот результат потребовалось включить сотрудников, проживающих в других местах, то нужно было применить левое внешнее соединение. Данное внешнее соединение называется левым потому, что оно возвращает все строки из таблицы с левой стороны оператора сравнения, независимо от того, имеются ли совпадающие строки в таблице с правой стороны. Иными словами, данное внешнее соединение возвратит строку с левой таблицы, даже если для нее нет совпадения в правой таблице, со значением NULL соответствующего столбца для всех строк с несовпадающим значением столбца другой, правой, таблицы. Для выполнения операции левого внешнего соединения компонент Database Engine использует оператор LEFT OUTER JOIN.
Операция правого внешнего соединения аналогична левому, но возвращаются все строки таблицы с правой части выражения. Для выполнения операции правого внешнего соединения компонент Database Engine использует оператор RIGHT OUTER JOIN.
В этом примере происходит выборка сотрудников (с включением полной информации) для таких городов, в которых сотрудники или только проживают (столбец City в таблице EmployeeEnh), или проживают и работают. Результат выполнения этого запроса:
Как можно видеть в результате выполнения запроса, когда для строки из левой таблицы (в данном случае EmployeeEnh) нет совпадающей строки в правой таблице (в данном случае Department), операция левого внешнего соединения все равно возвращает эту строку, заполняя значением NULL все ячейки соответствующего столбца для несовпадающего значения столбца правой таблицы. Применение правого внешнего соединения показано в примере ниже:
В этом примере происходит выборка отделов (с включением полной информации о них) для таких городов, в которых сотрудники или только работают, или проживают и работают. Результат выполнения этого запроса:
Кроме левого и правого внешнего соединения, также существует полное внешнее соединение, которое является объединением левого и правого внешних соединений. Иными словами, результирующий набор такого соединения состоит из всех строк обеих таблиц. Если для строки одной из таблиц нет соответствующей строки в другой таблице, всем ячейкам строки второй таблицы присваивается значение NULL. Для выполнения операции полного внешнего соединения используется оператор FULL OUTER JOIN.
Любую операцию внешнего соединения можно эмулировать, используя оператор UNION совместно с функцией NOT EXISTS. Таким образом, запрос, показанный в примере ниже, эквивалентен запросу левого внешнего соединения, показанному ранее. В данном запросе осуществляется выборка сотрудников (с включением полной информации) для таких городов, в которых сотрудники или только проживают или проживают и работают:
Первая инструкция SELECT объединения определяет естественное соединение таблиц EmployeeEnh и Department по столбцам соединения City и Location. Эта инструкция возвращает все города для всех сотрудников, в которых сотрудники и проживают и работают. Дополнительно, вторая инструкция SELECT объединения возвращает все строки таблицы EmployeeEnh, которые не отвечают условию в естественном соединении.
SQL Учебник
SQL ГлавнаяSQL ВведениеSQL СинтаксисSQL SELECTSQL SELECT DISTINCTSQL WHERESQL AND, OR, NOTSQL ORDER BYSQL INSERT INTOSQL Значение NullSQL Инструкция UPDATESQL Инструкция DELETESQL SELECT TOPSQL MIN() и MAX()SQL COUNT(), AVG() и …SQL Оператор LIKESQL ПодстановочныйSQL Оператор INSQL Оператор BETWEENSQL ПсевдонимыSQL JOINSQL JOIN ВнутриSQL JOIN СлеваSQL JOIN СправаSQL JOIN ПолноеSQL JOIN СамSQL Оператор UNIONSQL GROUP BYSQL HAVINGSQL Оператор ExistsSQL Операторы Any, AllSQL SELECT INTOSQL INSERT INTO SELECTSQL Инструкция CASESQL Функции NULLSQL ХранимаяSQL Комментарии
SQL Учебник
SQL ГлавнаяSQL ВведениеSQL СинтаксисSQL SELECTSQL SELECT DISTINCTSQL WHERESQL AND, OR, NOTSQL ORDER BYSQL INSERT INTOSQL Значение NullSQL Инструкция UPDATESQL Инструкция DELETESQL SELECT TOPSQL MIN() и MAX()SQL COUNT(), AVG() и …SQL Оператор LIKESQL ПодстановочныйSQL Оператор INSQL Оператор BETWEENSQL ПсевдонимыSQL JOINSQL JOIN ВнутриSQL JOIN СлеваSQL JOIN СправаSQL JOIN ПолноеSQL JOIN СамSQL Оператор UNIONSQL GROUP BYSQL HAVINGSQL Оператор ExistsSQL Операторы Any, AllSQL SELECT INTOSQL INSERT INTO SELECTSQL Инструкция CASESQL Функции NULLSQL ХранимаяSQL Комментарии
Example — Sorting Results by relative position
You can also use the SQL ORDER BY clause to sort by relative position in the result set, where the first field in the result set is 1, the second field is 2, the third field is 3, and so on.
In this example, we have a table called products with the following data:
| product_id | product_name | category_id |
|---|---|---|
| 1 | Pear | 50 |
| 2 | Banana | 50 |
| 3 | Orange | 50 |
| 4 | Apple | 50 |
| 5 | Bread | 75 |
| 6 | Sliced Ham | 25 |
| 7 | Kleenex | NULL |
Now enter the following SQL statement:
Try It
SELECT product_id, product_name FROM products WHERE product_name <> 'Bread' ORDER BY 1 DESC;
There will be 6 records selected. These are the results that you should see:
| product_id | product_name |
|---|---|
| 7 | Kleenex |
| 6 | Sliced Ham |
| 4 | Apple |
| 3 | Orange |
| 2 | Banana |
| 1 | Pear |
This example would sort the results by the product_id field in descending order, since the product_id field is in position #1 in the result set and would be equivalent to the following SQL ORDER BY clause:
Try It
MySQL ORDER BY Descending
To sort data in Descending order, use Order By statement followed by the DESC keyword. The following are the list of ways we can sort data in Descending order.
For example, If you are searching for shoes on Amazon. If you type shoe in the search bar, it displays shoes by Rating. It means, Shoes are sorted as per the Rating. Technically,
MySQL Sort in Descending Order Example
In this MySQL Order By Desc example, we are going to sort customer’s data in Descending Order using the Sales column.
From the above screenshot, you can see data sorted by Sales in Descending order.
MySQL Order By Multiple Columns in Descending Order
In this order by DESC example, we are sorting the Data using multiple columns. First, data sorted by Education in Descending Order and then sorted by Yearly Income in Descending Order.
Sort in Descending Order using Alias Column
In this MySQL Order By Desc example, We are going to sort the table Data in Descending Order using the Alias Column Name.
We added 12500 to each yearly income column and used Alias to assign a New Income name. Next, we used the Alias name in the Order By clause. It means, Data sort by New Income in Descending Order.
Database Tables
A database most often contains one or more tables. Each table is identified
by a name (e.g. «Customers» or «Orders»). Tables contain records (rows) with
data.
In this tutorial we will use the well-known Northwind sample database
(included in MS Access and MS SQL Server).
Below is a selection from the «Customers» table:
| CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
|---|---|---|---|---|---|---|
| 1 | Alfreds Futterkiste | Maria Anders | Obere Str. 57 | Berlin | 12209 | Germany |
| 2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
| 3 | Antonio Moreno Taquería | Antonio Moreno | Mataderos 2312 | México D.F. | 05023 | Mexico |
| 4 | Around the Horn | Thomas Hardy | 120 Hanover Sq. | London | WA1 1DP | UK |
| 5 | Berglunds snabbköp | Christina Berglund | Berguvsvägen 8 | Luleå | S-958 22 | Sweden |
The table above contains five records (one for each customer) and seven columns
(CustomerID, CustomerName, ContactName, Address, City, PostalCode, and Country).
SQL Учебник
SQL ГлавнаяSQL ВведениеSQL СинтаксисSQL SELECTSQL SELECT DISTINCTSQL WHERESQL AND, OR, NOTSQL ORDER BYSQL INSERT INTOSQL Значение NullSQL Инструкция UPDATESQL Инструкция DELETESQL SELECT TOPSQL MIN() и MAX()SQL COUNT(), AVG() и …SQL Оператор LIKESQL ПодстановочныйSQL Оператор INSQL Оператор BETWEENSQL ПсевдонимыSQL JOINSQL JOIN ВнутриSQL JOIN СлеваSQL JOIN СправаSQL JOIN ПолноеSQL JOIN СамSQL Оператор UNIONSQL GROUP BYSQL HAVINGSQL Оператор ExistsSQL Операторы Any, AllSQL SELECT INTOSQL INSERT INTO SELECTSQL Инструкция CASESQL Функции NULLSQL ХранимаяSQL Комментарии
Introduction to Oracle ORDER BY clause
In Oracle, a table stores its rows in unspecified order regardless of the order which rows were inserted into the database. To query rows in either ascending or descending order by a column, you must explicitly instruct Oracle Database that you want to do so.
For example, you may want to list all customers the by their names alphabetically or display all customers in order of lowest to highest credit limits.
To sort data, you add the clause to the statement as follows:
To sort the result set by a column, you list that column after the clause.
Following the column name is a sort order that can be:
- for sorting in ascending order
- for sorting in descending order
By default, the clause sorts rows in ascending order whether you specify or not. If you want to sort rows in descending order, you use explicitly.
places NULL values before non-NULL values and puts the NULL values after non-NULL values.
The clause allows you to sort data by multiple columns where each column may have different sort orders.
Note that the clause is always the last clause in a statement.
SQL Справочник
SQL Ключевые слова
ADD
ADD CONSTRAINT
ALTER
ALTER COLUMN
ALTER TABLE
ALL
AND
ANY
AS
ASC
BACKUP DATABASE
BETWEEN
CASE
CHECK
COLUMN
CONSTRAINT
CREATE
CREATE DATABASE
CREATE INDEX
CREATE OR REPLACE VIEW
CREATE TABLE
CREATE PROCEDURE
CREATE UNIQUE INDEX
CREATE VIEW
DATABASE
DEFAULT
DELETE
DESC
DISTINCT
DROP
DROP COLUMN
DROP CONSTRAINT
DROP DATABASE
DROP DEFAULT
DROP INDEX
DROP TABLE
DROP VIEW
EXEC
EXISTS
FOREIGN KEY
FROM
FULL OUTER JOIN
GROUP BY
HAVING
IN
INDEX
INNER JOIN
INSERT INTO
INSERT INTO SELECT
IS NULL
IS NOT NULL
JOIN
LEFT JOIN
LIKE
LIMIT
NOT
NOT NULL
OR
ORDER BY
OUTER JOIN
PRIMARY KEY
PROCEDURE
RIGHT JOIN
ROWNUM
SELECT
SELECT DISTINCT
SELECT INTO
SELECT TOP
SET
TABLE
TOP
TRUNCATE TABLE
UNION
UNION ALL
UNIQUE
UPDATE
VALUES
VIEW
WHERE
MySQL Функции
Функции строк
ASCII
CHAR_LENGTH
CHARACTER_LENGTH
CONCAT
CONCAT_WS
FIELD
FIND_IN_SET
FORMAT
INSERT
INSTR
LCASE
LEFT
LENGTH
LOCATE
LOWER
LPAD
LTRIM
MID
POSITION
REPEAT
REPLACE
REVERSE
RIGHT
RPAD
RTRIM
SPACE
STRCMP
SUBSTR
SUBSTRING
SUBSTRING_INDEX
TRIM
UCASE
UPPER
Функции чисел
ABS
ACOS
ASIN
ATAN
ATAN2
AVG
CEIL
CEILING
COS
COT
COUNT
DEGREES
DIV
EXP
FLOOR
GREATEST
LEAST
LN
LOG
LOG10
LOG2
MAX
MIN
MOD
PI
POW
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
SUM
TAN
TRUNCATE
Функции дат
ADDDATE
ADDTIME
CURDATE
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURTIME
DATE
DATEDIFF
DATE_ADD
DATE_FORMAT
DATE_SUB
DAY
DAYNAME
DAYOFMONTH
DAYOFWEEK
DAYOFYEAR
EXTRACT
FROM_DAYS
HOUR
LAST_DAY
LOCALTIME
LOCALTIMESTAMP
MAKEDATE
MAKETIME
MICROSECOND
MINUTE
MONTH
MONTHNAME
NOW
PERIOD_ADD
PERIOD_DIFF
QUARTER
SECOND
SEC_TO_TIME
STR_TO_DATE
SUBDATE
SUBTIME
SYSDATE
TIME
TIME_FORMAT
TIME_TO_SEC
TIMEDIFF
TIMESTAMP
TO_DAYS
WEEK
WEEKDAY
WEEKOFYEAR
YEAR
YEARWEEK
Функции расширений
BIN
BINARY
CASE
CAST
COALESCE
CONNECTION_ID
CONV
CONVERT
CURRENT_USER
DATABASE
IF
IFNULL
ISNULL
LAST_INSERT_ID
NULLIF
SESSION_USER
SYSTEM_USER
USER
VERSION
SQL Server функции
Функции строк
ASCII
CHAR
CHARINDEX
CONCAT
Concat with +
CONCAT_WS
DATALENGTH
DIFFERENCE
FORMAT
LEFT
LEN
LOWER
LTRIM
NCHAR
PATINDEX
QUOTENAME
REPLACE
REPLICATE
REVERSE
RIGHT
RTRIM
SOUNDEX
SPACE
STR
STUFF
SUBSTRING
TRANSLATE
TRIM
UNICODE
UPPER
Функции чисел
ABS
ACOS
ASIN
ATAN
ATN2
AVG
CEILING
COUNT
COS
COT
DEGREES
EXP
FLOOR
LOG
LOG10
MAX
MIN
PI
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
SQUARE
SUM
TAN
Функции дат
CURRENT_TIMESTAMP
DATEADD
DATEDIFF
DATEFROMPARTS
DATENAME
DATEPART
DAY
GETDATE
GETUTCDATE
ISDATE
MONTH
SYSDATETIME
YEAR
Функции расширений
CAST
COALESCE
CONVERT
CURRENT_USER
IIF
ISNULL
ISNUMERIC
NULLIF
SESSION_USER
SESSIONPROPERTY
SYSTEM_USER
USER_NAME
MS Access функции
Функции строк
Asc
Chr
Concat with &
CurDir
Format
InStr
InstrRev
LCase
Left
Len
LTrim
Mid
Replace
Right
RTrim
Space
Split
Str
StrComp
StrConv
StrReverse
Trim
UCase
Функции чисел
Abs
Atn
Avg
Cos
Count
Exp
Fix
Format
Int
Max
Min
Randomize
Rnd
Round
Sgn
Sqr
Sum
Val
Функции дат
Date
DateAdd
DateDiff
DatePart
DateSerial
DateValue
Day
Format
Hour
Minute
Month
MonthName
Now
Second
Time
TimeSerial
TimeValue
Weekday
WeekdayName
Year
Другие функции
CurrentUser
Environ
IsDate
IsNull
IsNumeric
SQL ОператорыSQL Типы данныхSQL Краткий справочник
MySQL ORDER BY examples
We’ll use the table from the sample database for the demonstration.
A) Using MySQL ORDER BY clause to sort the result set by one column example
The following query uses the clause to sort the customers by their last names in ascending order.
Output:
If you want to sort customers by the last name in descending order, you use the after the column in the clause as shown in the following query:
Ouptut:
B) Using MySQL ORDER BY clause to sort the result set by multiple columns example
If you want to sort the customers by the last name in descending order and then by the first name in ascending order, you specify both and in these respective columns as follows:
Output:
In this example, the clause sorts the result set by the last name in descending order first and then sorts the sorted result set by the first name in ascending order to make the final result set.
C) Using MySQL ORDER BY clause to sort a result set by an expression example
See the following table from the sample database.
The following query selects the order line items from the table. It calculates the subtotal for each line item and sorts the result set based on the subtotal.
To make the query more readable, you can assign the expression in the clause a column alias and use that column alias in the clause as shown in the following query:
In this example, we use as the column alias for the expression and sort the result set by the alias.
Since MySQL evaluates the clause before the clause, you can use the column alias specified in the clause in the clause.
Sort Operations and Clustered Index
A clustered index sorts the data rows based on the specified key columns so that the clustered index creates a
sorted data structure of the table. Only one clustered index can be defined for a table because the data rows can be
sorted in just one way. When we used the clustered key column after the ORDER BY clause, the query
optimizer may not need to use the sort operator because of the clustered index structure.
For example, the following query retrieves some columns of the SalesOrderHeaderEnlarged table and
it also sorts the result set ascending order according to the SalesOrderID column.
|
1 |
SELECTSalesOrderID,CreditCardApprovalCode,AccountNumber FROMSales.SalesOrderHeaderEnlarged ORDERBYSalesOrderIDASC |
In the execution plan, we see an Ordered attribute of the clustered index scan operator as true.
With the help of this attribute, we can understand that the storage engine has returned the data rows in a sorted
manner.
On the other hand, when we don’t use the ORDER BY statement explicitly in the queries, the clustered index does not
guarantee to return data rows in a pre-sorted fashion. Such as ;
Allocation Order Scan: SQL Server storage engine can access the data using either the b-tree
structure or Index Allocation Map (IAM) through the clustered indexes. Allocation order scan returns the data rows
in an unpredictable order. The NOLOCK hint can cause to drive this scan type when it also meets the
following conditions:
- The index size is greater than 64 pages
- The ordered attribute of the index scan operator is false
This query uses the b-tree structure to retrieve data rows.
|
1 |
SELECT BusinessEntityID, PersonType, MiddleName,LastName FROMPerson.Person
|
Now we will add the NOLOCK hint to the query.
|
1 |
SELECT BusinessEntityID, PersonType, MiddleName,LastName FROMPerson.PersonWITH(TABLOCK) |
In the execution plan of this query, we will see that the ordered attribute is false.
Parallel Query Plans: The parallel query plan enables to separate the big data processing task into
small ones. In this way, a query is processed by more than one thread so that the query completion time will be
reduced. However, the sorting of data rows combined in the last stage of the parallel plans is not predictable if we
don’t use the ORDER BY statement.
SQL Справочник
SQL Ключевые слова
ADD
ADD CONSTRAINT
ALTER
ALTER COLUMN
ALTER TABLE
ALL
AND
ANY
AS
ASC
BACKUP DATABASE
BETWEEN
CASE
CHECK
COLUMN
CONSTRAINT
CREATE
CREATE DATABASE
CREATE INDEX
CREATE OR REPLACE VIEW
CREATE TABLE
CREATE PROCEDURE
CREATE UNIQUE INDEX
CREATE VIEW
DATABASE
DEFAULT
DELETE
DESC
DISTINCT
DROP
DROP COLUMN
DROP CONSTRAINT
DROP DATABASE
DROP DEFAULT
DROP INDEX
DROP TABLE
DROP VIEW
EXEC
EXISTS
FOREIGN KEY
FROM
FULL OUTER JOIN
GROUP BY
HAVING
IN
INDEX
INNER JOIN
INSERT INTO
INSERT INTO SELECT
IS NULL
IS NOT NULL
JOIN
LEFT JOIN
LIKE
LIMIT
NOT
NOT NULL
OR
ORDER BY
OUTER JOIN
PRIMARY KEY
PROCEDURE
RIGHT JOIN
ROWNUM
SELECT
SELECT DISTINCT
SELECT INTO
SELECT TOP
SET
TABLE
TOP
TRUNCATE TABLE
UNION
UNION ALL
UNIQUE
UPDATE
VALUES
VIEW
WHERE
MySQL Функции
Функции строк
ASCII
CHAR_LENGTH
CHARACTER_LENGTH
CONCAT
CONCAT_WS
FIELD
FIND_IN_SET
FORMAT
INSERT
INSTR
LCASE
LEFT
LENGTH
LOCATE
LOWER
LPAD
LTRIM
MID
POSITION
REPEAT
REPLACE
REVERSE
RIGHT
RPAD
RTRIM
SPACE
STRCMP
SUBSTR
SUBSTRING
SUBSTRING_INDEX
TRIM
UCASE
UPPER
Функции чисел
ABS
ACOS
ASIN
ATAN
ATAN2
AVG
CEIL
CEILING
COS
COT
COUNT
DEGREES
DIV
EXP
FLOOR
GREATEST
LEAST
LN
LOG
LOG10
LOG2
MAX
MIN
MOD
PI
POW
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
SUM
TAN
TRUNCATE
Функции дат
ADDDATE
ADDTIME
CURDATE
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURTIME
DATE
DATEDIFF
DATE_ADD
DATE_FORMAT
DATE_SUB
DAY
DAYNAME
DAYOFMONTH
DAYOFWEEK
DAYOFYEAR
EXTRACT
FROM_DAYS
HOUR
LAST_DAY
LOCALTIME
LOCALTIMESTAMP
MAKEDATE
MAKETIME
MICROSECOND
MINUTE
MONTH
MONTHNAME
NOW
PERIOD_ADD
PERIOD_DIFF
QUARTER
SECOND
SEC_TO_TIME
STR_TO_DATE
SUBDATE
SUBTIME
SYSDATE
TIME
TIME_FORMAT
TIME_TO_SEC
TIMEDIFF
TIMESTAMP
TO_DAYS
WEEK
WEEKDAY
WEEKOFYEAR
YEAR
YEARWEEK
Функции расширений
BIN
BINARY
CASE
CAST
COALESCE
CONNECTION_ID
CONV
CONVERT
CURRENT_USER
DATABASE
IF
IFNULL
ISNULL
LAST_INSERT_ID
NULLIF
SESSION_USER
SYSTEM_USER
USER
VERSION
SQL Server функции
Функции строк
ASCII
CHAR
CHARINDEX
CONCAT
Concat with +
CONCAT_WS
DATALENGTH
DIFFERENCE
FORMAT
LEFT
LEN
LOWER
LTRIM
NCHAR
PATINDEX
QUOTENAME
REPLACE
REPLICATE
REVERSE
RIGHT
RTRIM
SOUNDEX
SPACE
STR
STUFF
SUBSTRING
TRANSLATE
TRIM
UNICODE
UPPER
Функции чисел
ABS
ACOS
ASIN
ATAN
ATN2
AVG
CEILING
COUNT
COS
COT
DEGREES
EXP
FLOOR
LOG
LOG10
MAX
MIN
PI
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
SQUARE
SUM
TAN
Функции дат
CURRENT_TIMESTAMP
DATEADD
DATEDIFF
DATEFROMPARTS
DATENAME
DATEPART
DAY
GETDATE
GETUTCDATE
ISDATE
MONTH
SYSDATETIME
YEAR
Функции расширений
CAST
COALESCE
CONVERT
CURRENT_USER
IIF
ISNULL
ISNUMERIC
NULLIF
SESSION_USER
SESSIONPROPERTY
SYSTEM_USER
USER_NAME
MS Access функции
Функции строк
Asc
Chr
Concat with &
CurDir
Format
InStr
InstrRev
LCase
Left
Len
LTrim
Mid
Replace
Right
RTrim
Space
Split
Str
StrComp
StrConv
StrReverse
Trim
UCase
Функции чисел
Abs
Atn
Avg
Cos
Count
Exp
Fix
Format
Int
Max
Min
Randomize
Rnd
Round
Sgn
Sqr
Sum
Val
Функции дат
Date
DateAdd
DateDiff
DatePart
DateSerial
DateValue
Day
Format
Hour
Minute
Month
MonthName
Now
Second
Time
TimeSerial
TimeValue
Weekday
WeekdayName
Year
Другие функции
CurrentUser
Environ
IsDate
IsNull
IsNumeric
SQL ОператорыSQL Типы данныхSQL Краткий справочник
Example: Sort by ordinal positions of columns
The SQL Server allows you to sort the result set based on the ordinal positions of columns that appear in the select list.
There is the following statement sorts the Employee by EmployeeName and FatherName. But instead of specifying the column names explicitly, furthermore, it will use the ordinal positions of the columns:
SELECT
EmployeeName,
FatherName
FROM
Employees
ORDER BY
1,
2;
|
1 |
SELECT EmployeeName, FatherName Employees ORDERBY 1, 2; |
Output:
In this above example, 1 (First) means the column and 2 (Second) means the .
Therefore, Using the ordinal positions of columns in the clause, which is considered a bad programming practice or exercise for a couple of reasons. First, the columns in a table don’t have ordinal positions and need to be referenced by the name. Second, when you modify the select list, you may forget to make the corresponding changes in the SQL ORDER BY clause.
Example
Question 1: Arrange in alphabetical order
SELECT CompanyName, ContactName, City, Country
FROM Supplier
ORDER BY CompanyName
|
1 |
SELECTCompanyName,ContactName,City,Country FROMSupplier ORDERBYCompanyName |
| Supplier |
|---|
| CompanyName |
| ContactName |
| City |
| Country |
Demo table
| Id | CompanyName | ContactName | City | Country |
|---|---|---|---|---|
| 121 | Amdocs Pvt. Ltd | Cheryl Saylor | Delhi | India |
| 131 | Ascon Group | Dolly Ben | New Delhi | India |
| 101 | Lalit Pvt.Ltd | Nitin Shukla | Lucknow | India |
| 231 | Singsymsis Pvt.Ltd | Aditya Shukla | Kanpur | India |
Question 2: SQL Case statement For Order By clause with Desc/Asc sort
SELECT
*
FROM
TableName
WHERE
ORDER BY
CASE @OrderByColumn WHEN 1 THEN Forename END DESC, Date,
CASE @OrderByColumn WHEN 2 THEN Surname END ASC
|
1 |
SELECT TableName WHERE ORDER BY CASE@OrderByColumn WHEN1THENForename ENDDESC,Date, CASE@OrderByColumn WHEN2THENSurname ENDASC |
Resource
Conclusion
In this article, you have learned how to use the SQL ORDER BY clause to sort a result set by columns in descending or ascending order and with the different types of ORDER BY Clause example. I hope you will enjoy it!
Oracle ORDER BY clause examples
We will use the table in the sample database for demonstration.
The following statement retrieves customer name, address, and credit limit from the table:
As you can see, the order of rows is unspecified.
A) Sorting rows by a column example
To sort the customer data by names alphabetically in ascending order, you use the following statement:
The instructs Oracle to sort the rows in ascending order. Because the is optional. If you omit it, by default, the clause sorts rows by the specified column in ascending order.
Therefore, the following expression:
is equivalent to the following:
To sort customer by name alphabetically in descending order, you explicitly use after the column name in the clause as follows:
The following picture shows the result that customers sorted by names alphabetically in descending order:
B) Sorting rows by multiple columns example
To sort multiple columns, you separate each column in the clause by a comma.
See the following table in the sample database.
For example, to sort contacts by their first names in ascending order and their last names in descending order, you use the following statement:
In this example, Oracle first sorts the rows by first names in ascending order to make an initial result set. Oracle then sorts the initial result set by the last name in descending order.
See the following result:
In this result:
- First, the first names are sorted in ascending order.
- Second, if two first names are the same, the last names are sorted in descending order e..g, and , and , and .
C) Sort rows by column’s positions example
You don’t need to specify the column names for sorting the data. If you prefer, you can use the positions of the column in the clause.
See the following statement:
In this example, the position of column is 1 and column is 2.
In the clause, we used these column positions to instruct the Oracle to sort the rows.
C) Sorting rows with NULL values examples
See the following table in the sample database:
The following statement retrieves locations and sorts them by city and state:
Here is the result:
The column has values, meaning that the state data is not relevant to some cities e.g., Beijing, Hiroshima, and London.
When sorting mixed with non-NULL values, Oracle allows you to specify which one should appear first.
For example, the following statement sorts the locations by state in ascending order and places NULL values first.
To place NULL values after the non-NULL values, you use as shown in the following statement:
Here is the result:
D) Sorting rows by the result of a function or expression
The clause allows you to apply a function e.g., string function and math function on a column and sorts the data by the result of the function.
For example, the following statement uses the function in the clause to sort the customer names case-insensitively:
The following illustrates the result:
E) Sorting by date example
See the following table from the sample database:
This example uses the clause to sort orders by order date:
In this tutorial, you have learned how to use the Oracle clause to sort rows by one or more columns in ascending or descending order.
Sorting results in ascending order
To sort the results in ascending order, you can specify the ASC attribute. If no value (ASC or DESC) is specified after the field in the ORDER BY sentence, the sort order will be in ascending order by default. Let’s look at this further.In this example, we have a table with the following data:
| cust_id | f_name | l_name | fav_website |
|---|---|---|---|
| 4000 | Justin | Bieber | google.com |
| 5000 | Selena | Gomez | bing.com |
| 6000 | Mila | Kunis | yahoo.com |
| 7000 | Tom | Cruise | oracle.com |
| 8000 | Johnny | Depp | NULL |
| 9000 | Russell | Crowe | google.com |
Enter the following SQL statement:
Six records will be selected. Here are the results that you should get.
| cust_id | f_name | l_name | fav_website |
|---|---|---|---|
| 4000 | Justin | Bieber | google.com |
| 9000 | Russell | Crowe | google.com |
| 7000 | Tom | Cruise | oracle.com |
| 8000 | Johnny | Depp | NULL |
| 5000 | Selena | Gomez | bing.com |
| 6000 | Mila | Kunis | yahoo.com |
In this example, all records from the customer table will be returned, sorted by l_name field in ascending order, and will be equivalent to the next ORDER BY SQL sentence.
Most programmers skip the ASC attribute when sorting in ascending order.
SQL Учебник
SQL ГлавнаяSQL ВведениеSQL СинтаксисSQL SELECTSQL SELECT DISTINCTSQL WHERESQL AND, OR, NOTSQL ORDER BYSQL INSERT INTOSQL Значение NullSQL Инструкция UPDATESQL Инструкция DELETESQL SELECT TOPSQL MIN() и MAX()SQL COUNT(), AVG() и …SQL Оператор LIKESQL ПодстановочныйSQL Оператор INSQL Оператор BETWEENSQL ПсевдонимыSQL JOINSQL JOIN ВнутриSQL JOIN СлеваSQL JOIN СправаSQL JOIN ПолноеSQL JOIN СамSQL Оператор UNIONSQL GROUP BYSQL HAVINGSQL Оператор ExistsSQL Операторы Any, AllSQL SELECT INTOSQL INSERT INTO SELECTSQL Инструкция CASESQL Функции NULLSQL ХранимаяSQL Комментарии
