Sql server order by
Содержание:
- SQL Учебник
- Пример — сортировка результатов по убыванию
- Example 4 – ORDER BY Column Number
- Пример — сортировка без использования атрибута ASC / DESC
- SQL References
- SQL References
- SQL Учебник
- SQL SELECT TOP
- SQL Учебник
- SQL Учебник
- Example — Using both ASC and DESC attributes
- SQL Справочник
- Database Tables
- SQL Учебник
- Introduction to the SQL Server ORDER BY clause
- SQL Справочник
- Examples
- Синтаксис
- MySQL ORDER BY Descending
- SQL Учебник
- UPDATE таблица
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 Комментарии
Пример — сортировка результатов по убыванию
При сортировке набора результатов в порядке убывания вы используете атрибут DESC в предложении ORDER BY. Давайте внимательнее посмотрим.
В этом примере у нас есть таблица suppliers со следующими данными:
| supplier_id | supplier_name | city | state |
|---|---|---|---|
| 100 | Yandex | Moscow | Russian |
| 200 | Lansing | Michigan | |
| 300 | Oracle | Redwood City | California |
| 400 | Bing | Redmond | Washington |
| 500 | Yahoo | Sunnyvale | Washington |
| 600 | DuckDuckGo | Paoli | Pennsylvania |
| 700 | Qwant | Paris | France |
| 800 | Menlo Park | California | |
| 900 | Electronic Arts | San Francisco | California |
Введите следующий SQL оператор.
PgSQL
SELECT *
FROM suppliers
WHERE supplier_id > 40
ORDER BY supplier_id DESC;
|
1 |
SELECT* FROMsuppliers WHEREsupplier_id>40 ORDERBYsupplier_idDESC; |
Будет выбрано 5 записей. Вот результаты, которые вы должны получить.
| supplier_id | supplier_name | city | state |
|---|---|---|---|
| 900 | Electronic Arts | San Francisco | California |
| 800 | Menlo Park | California | |
| 700 | Qwant | Paris | France |
| 600 | DuckDuckGo | Paoli | Pennsylvania |
| 500 | Yahoo | Sunnyvale | Washington |
В этом примере будет отсортирован набор результатов по полю supplier_id в порядке убывания.
Example 4 – ORDER BY Column Number
This example shows you how to order your results by specifying a column number.
| SALESPERSON_ID | FIRST_NAME | SALARY | COMMISSION | HIRE_DATE |
| 4 | Tina | 87000 | 900 | 24-Oct-16 |
| 5 | Steve | 100000 | 500 | 2-Feb-16 |
| 2 | Sally | 95000 | 500 | 5-Sep-16 |
| 6 | Michelle | 120000 | 600 | 3-Dec-16 |
| 3 | Mark | 101000 | 800 | 12-Aug-16 |
| 1 | John | 90000 | 1000 | 1-Jan-16 |
| 8 | Jo | 115000 | 1200 | 30-Oct-16 |
| 7 | Alex | 85000 | (null) | 17-Jan-16 |
This sorts the results by the second column in the SELECT clause. In this case, it is the first_name column (salesperson_id is column 1, first_name is column 2, salary is 3, and so on).
This is the order of the columns in the SELECT clause, and not the table.
I try to avoid using column numbers when ordering, as it’s not clear which column is being ordered. Also, if the sequence that the columns are mentioned in the SELECT clause is changed, then the ordering will break, or order by the incorrect values.
Example 5 – ORDER BY a Column Alias
This example shows you how you can order by a column alias. This is very helpful to reduce the amount of code you write and to keep your logic in one place (the SELECT clause, for example).
| SALESPERSON_ID | FIRST_NAME | TOTAL_EARNINGS |
| 7 | Alex | (null) |
| 6 | Michelle | 120600 |
| 8 | Jo | 116200 |
| 3 | Mark | 101800 |
| 5 | Steve | 100500 |
| 2 | Sally | 95500 |
| 1 | John | 91000 |
| 4 | Tina | 87900 |
This query shows the salespeople in order of their total earnings. Using a column alias in the SQL ORDER BY clause is helpful, especially when working with complicated functions.
Example 6 – ORDER BY an Expression
This example shows how you can order by an expression.
| SALESPERSON_ID | FIRST_NAME | SALARY/52 |
| 6 | Michelle | 2307.692308 |
| 8 | Jo | 2211.538462 |
| 3 | Mark | 1942.307692 |
| 5 | Steve | 1923.076923 |
| 2 | Sally | 1826.923077 |
| 1 | John | 1730.769231 |
| 4 | Tina | 1673.076923 |
| 7 | Alex | 1634.615385 |
This query shows the weekly salary of each salesperson.
Example 7 – ORDER BY Function
This example shows how you can use ORDER BY with a function.
| SALESPERSON_ID | FIRST_NAME | FLOOR(SALARY/52) |
| 6 | Michelle | 2307 |
| 8 | Jo | 2211 |
| 3 | Mark | 1942 |
| 5 | Steve | 1923 |
| 2 | Sally | 1826 |
| 1 | John | 1730 |
| 4 | Tina | 1673 |
| 7 | Alex | 1634 |
You can see that the results have been ordered by the function value.
Example 8 – ORDER BY with NULLS FIRST
This example shows how the NULLS FIRST keyword works.
| SALESPERSON_ID | FIRST_NAME | SALARY | COMMISSION |
| 7 | Alex | 85000 | (null) |
| 2 | Sally | 95000 | 500 |
| 5 | Steve | 100000 | 500 |
| 6 | Michelle | 120000 | 600 |
| 3 | Mark | 101000 | 800 |
| 4 | Tina | 87000 | 900 |
| 1 | John | 90000 | 1000 |
| 8 | Jo | 115000 | 1200 |
As you can see, the NULL values are shown at the top of the list.
Example 9 – ORDER BY with NULLS LAST
This example shows how the NULLS LAST keyword works.
| SALESPERSON_ID | FIRST_NAME | SALARY | COMMISSION |
| 2 | Sally | 95000 | 500 |
| 5 | Steve | 100000 | 500 |
| 6 | Michelle | 120000 | 600 |
| 3 | Mark | 101000 | 800 |
| 4 | Tina | 87000 | 900 |
| 1 | John | 90000 | 1000 |
| 8 | Jo | 115000 | 1200 |
| 7 | Alex | 85000 | (null) |
As you can see, the NULL values are shown at the bottom of the list.
Example 10 – ORDER BY Two Columns
This example shows how you can use SQL ORDER BY with two columns.
| SALESPERSON_ID | FIRST_NAME | SALARY | COMMISSION |
| 2 | Sally | 95000 | 500 |
| 5 | Steve | 100000 | 500 |
| 6 | Michelle | 120000 | 600 |
| 3 | Mark | 101000 | 800 |
| 4 | Tina | 87000 | 900 |
| 1 | John | 90000 | 1000 |
| 8 | Jo | 115000 | 1200 |
| 7 | Alex | 85000 | (null) |
This query orders by the commission values in ascending order, then for records where the commission is the same, it orders by salary in ascending order.
Example 11 – ORDER BY Two Columns in Different Order
This example orders by two columns, but they are in a different order.
| SALESPERSON_ID | FIRST_NAME | SALARY | COMMISSION |
| 5 | Steve | 100000 | 500 |
| 2 | Sally | 95000 | 500 |
| 6 | Michelle | 120000 | 600 |
| 3 | Mark | 101000 | 800 |
| 4 | Tina | 87000 | 900 |
| 1 | John | 90000 | 1000 |
| 8 | Jo | 115000 | 1200 |
| 7 | Alex | 85000 | (null) |
This query orders by the commission values in ascending order, then for records where the commission is the same, it orders by salary in descending order.
So, that’s how you can use the SQL ORDER BY clause in Oracle SQL to order your results. It’s a powerful clause and has a few keywords to get you the result that you need.
Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!
Пример — сортировка без использования атрибута ASC / DESC
PostgreSQL оператор ORDER BY можно использовать без указания модификатора ASC или DESC. Когда этот атрибут опущен в операторе ORDER BY, порядок сортировки по умолчанию устанавливается в ASC (в порядке возрастания).
Например:
PgSQL
SELECT last_name, first_name
FROM contacts
WHERE state = ‘California’
ORDER BY last_name;
|
1 |
SELECTlast_name,first_name FROMcontacts WHEREstate=’California’ ORDERBYlast_name; |
Этот пример будет возвращать все записи, отсортированные по полю last_name в порядке возрастания, и будет эквивалентен следующему оператору ORDER BY:
PgSQL
SELECT last_name, first_name
FROM contacts
WHERE state = ‘California’
ORDER BY last_name ASC;
|
1 |
SELECTlast_name,first_name FROMcontacts WHEREstate=’California’ ORDERBYlast_nameASC; |
Большинство программистов пропускают атрибут ASC при сортировке в порядке возрастания.
SQL References
SQL Keywords
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 Functions
String Functions
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
Numeric Functions
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
Date Functions
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
Advanced Functions
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 Functions
String Functions
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
Numeric Functions
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
Date Functions
CURRENT_TIMESTAMP
DATEADD
DATEDIFF
DATEFROMPARTS
DATENAME
DATEPART
DAY
GETDATE
GETUTCDATE
ISDATE
MONTH
SYSDATETIME
YEAR
Advanced Functions
CAST
COALESCE
CONVERT
CURRENT_USER
IIF
ISNULL
ISNUMERIC
NULLIF
SESSION_USER
SESSIONPROPERTY
SYSTEM_USER
USER_NAME
MS Access Functions
String Functions
Asc
Chr
Concat with &
CurDir
Format
InStr
InstrRev
LCase
Left
Len
LTrim
Mid
Replace
Right
RTrim
Space
Split
Str
StrComp
StrConv
StrReverse
Trim
UCase
Numeric Functions
Abs
Atn
Avg
Cos
Count
Exp
Fix
Format
Int
Max
Min
Randomize
Rnd
Round
Sgn
Sqr
Sum
Val
Date Functions
Date
DateAdd
DateDiff
DatePart
DateSerial
DateValue
Day
Format
Hour
Minute
Month
MonthName
Now
Second
Time
TimeSerial
TimeValue
Weekday
WeekdayName
Year
Other Functions
CurrentUser
Environ
IsDate
IsNull
IsNumeric
SQL Quick Ref
SQL References
SQL Keywords
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 Functions
String Functions
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
Numeric Functions
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
Date Functions
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
Advanced Functions
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 Functions
String Functions
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
Numeric Functions
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
Date Functions
CURRENT_TIMESTAMP
DATEADD
DATEDIFF
DATEFROMPARTS
DATENAME
DATEPART
DAY
GETDATE
GETUTCDATE
ISDATE
MONTH
SYSDATETIME
YEAR
Advanced Functions
CAST
COALESCE
CONVERT
CURRENT_USER
IIF
ISNULL
ISNUMERIC
NULLIF
SESSION_USER
SESSIONPROPERTY
SYSTEM_USER
USER_NAME
MS Access Functions
String Functions
Asc
Chr
Concat with &
CurDir
Format
InStr
InstrRev
LCase
Left
Len
LTrim
Mid
Replace
Right
RTrim
Space
Split
Str
StrComp
StrConv
StrReverse
Trim
UCase
Numeric Functions
Abs
Atn
Avg
Cos
Count
Exp
Fix
Format
Int
Max
Min
Randomize
Rnd
Round
Sgn
Sqr
Sum
Val
Date Functions
Date
DateAdd
DateDiff
DatePart
DateSerial
DateValue
Day
Format
Hour
Minute
Month
MonthName
Now
Second
Time
TimeSerial
TimeValue
Weekday
WeekdayName
Year
Other Functions
CurrentUser
Environ
IsDate
IsNull
IsNumeric
SQL Quick Ref
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 SELECT TOP
Инструкция SELECT TOP используется для указания количества возвращаемых записей.
Инструкция SELECT TOP полезно для больших таблиц с тысячами записей. Возврат большого количества записей может повлиять на производительность.
Примечание: Не все базы данных поддерживают SELECT TOP.
MySQL поддерживает предложение LIMIT для выбора ограниченного числа записей, в то время как Oracle использует ROWNUM.
Синтаксис SQL Server / MS Access:
SELECT TOP number|percent column_name(s)
FROM table_nameWHERE condition;
Синтаксис MySQL:
SELECT column_name(s)
FROM table_nameWHERE condition
LIMIT number;
Синтаксис Oracle:
SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;
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 — Using both ASC and DESC attributes
When sorting your result set using the SQL ORDER BY clause, you can use the ASC and DESC attributes in a single SELECT statement.
In this example, let’s use the same products table as the previous example:
| 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 * FROM products WHERE product_id <> 7 ORDER BY category_id DESC, product_name ASC;
There will be 6 records selected. These are the results that you should see:
| product_id | product_name | category_id |
|---|---|---|
| 5 | Bread | 75 |
| 4 | Apple | 50 |
| 2 | Banana | 50 |
| 3 | Orange | 50 |
| 1 | Pear | 50 |
| 6 | Sliced Ham | 25 |
This example would return the records sorted by the category_id field in descending order, with a secondary sort by product_name in ascending order.
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 Краткий справочник
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 the SQL Server ORDER BY clause
When you use the statement to query data from a table, the order of rows in the result set is not guaranteed. It means that SQL Server can return a result set with an unspecified order of rows.
The only way for you to guarantee that the rows in the result set are sorted is to use the clause. The following illustrates the clause syntax:
In this syntax:
|
First, you specify a column name or an expression on which to sort the result set of the query. If you specify multiple columns, the result set is sorted by the first column and then that sorted result set is sorted by the second column, and so on.
The columns that appear in the clause must correspond to either column in the select list or to columns defined in the table specified in the clause.
|
Second, use or to specify the whether the values in the specified column should be sorted in ascending or descending order.
The sorts the result from the lowest value to the highest value while the sorts the result set from the highest value to the lowest one.
If you don’t explicitly specify or , SQL Server uses as the default sort order. Also, SQL Server treats NULL as the lowest values.
When processing the statement that has an clause, the clause is the very last clause to be processed.
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 Краткий справочник
Examples
I think the easiest way to learn is to see examples. So, I’ll show you a few ways you can use the SQL ORDER BY clause in Oracle.
I’ll be using this table to perform the SELECT queries on.
| SALESPERSON_ID | FIRST_NAME | SALARY | COMMISSION | HIRE_DATE |
| 1 | John | 90000 | 1000 | 1-Jan-16 |
| 2 | Sally | 95000 | 500 | 5-Sep-16 |
| 3 | Mark | 101000 | 800 | 12-Aug-16 |
| 4 | Tina | 87000 | 900 | 24-Oct-16 |
| 5 | Steve | 100000 | 500 | 2-Feb-16 |
| 6 | Michelle | 120000 | 600 | 3-Dec-16 |
| 7 | Alex | 85000 | (null) | 17-Jan-16 |
| 8 | Jo | 115000 | 1200 | 30-Oct-16 |
Example 1 – ORDER BY Column Name
This example orders by a single column name.
| SALESPERSON_ID | FIRST_NAME | SALARY | COMMISSION | HIRE_DATE |
| 7 | Alex | 85000 | (null) | 17-Jan-16 |
| 8 | Jo | 115000 | 1200 | 30-Oct-16 |
| 1 | John | 90000 | 1000 | 1-Jan-16 |
| 3 | Mark | 101000 | 800 | 12-Aug-16 |
| 6 | Michelle | 120000 | 600 | 3-Dec-16 |
| 2 | Sally | 95000 | 500 | 5-Sep-16 |
| 5 | Steve | 100000 | 500 | 2-Feb-16 |
| 4 | Tina | 87000 | 900 | 24-Oct-16 |
All of the records are ordered by the first_name column. ASC or DESC was not specified, so by default, they are ordered in ASC order.
Example 2 – ORDER BY Column Name using ASC
This example orders by a column name and uses the ASC keyword.
| SALESPERSON_ID | FIRST_NAME | SALARY | COMMISSION | HIRE_DATE |
| 7 | Alex | 85000 | (null) | 17-Jan-16 |
| 4 | Tina | 87000 | 900 | 24-Oct-16 |
| 1 | John | 90000 | 1000 | 1-Jan-16 |
| 2 | Sally | 95000 | 500 | 5-Sep-16 |
| 5 | Steve | 100000 | 500 | 2-Feb-16 |
| 3 | Mark | 101000 | 800 | 12-Aug-16 |
| 8 | Jo | 115000 | 1200 | 30-Oct-16 |
| 6 | Michelle | 120000 | 600 | 3-Dec-16 |
This sorts the data in the table by salary in ascending order, which for numbers, is from smallest to highest.
Синтаксис
Синтаксис для предложения ORDER BY в PostgreSQL:
SELECT expressions
FROM tables
ORDER BY expression ;
Параметры или аргументы
- expressions
- Столбцы или вычисления, которые вы хотите получить.
- tables
- Таблицы, из которых вы хотите получить записи. В операторе FROM должна быть указана хотя бы одна таблица.
- WHERE conditions
- Необязательный. Условия, которые должны быть выполнены для записей, которые будут выбраны.
- ASC
- Необязательный. Он сортирует результирующий набор в порядке возрастания по expression (по умолчанию, если модификатор не указан).
- DESC
- Необязательный. Он сортирует результирующий набор в порядке убывания по expression.
- NULLS FIRST
- Необязательный. Если указано, все значения NULL сортируются перед значениями, отличных от NULL, в результирующем наборе.
- NULLS LAST
- Необязательный. Если указано, все значения NULL сортируются после значений, отличных от NULL, в результирующем наборе.
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.
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 Комментарии
UPDATE таблица
Следующая инструкция SQL обновляет первого клиента (CustomerID = 1) с новым контактным лицом и новым городом.
Пример
UPDATE Customers
SET ContactName = ‘Alfred Schmidt’, City= ‘Frankfurt’
WHERE CustomerID = 1;
Выбор из таблицы «Customers» теперь будет выглядеть следующим образом:
| CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
|---|---|---|---|---|---|---|
| 1 | Alfreds Futterkiste | Alfred Schmidt | Obere Str. 57 | Frankfurt | 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 |
