Select (transact-sql)select (transact-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 Комментарии

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.

The Numerical position of Education column is 3. So, data sorted by this column in Descending order.

OUTPUT

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.

OUTPUT

MySQL 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 Ограничение UNIQUE в CREATE TABLE

Следующий SQL создает уникальное ограничение для столбца «ID» при создании таблицы «Persons»:

SQL Server / Oracle / MS Access:

CREATE TABLE Persons (     ID int NOT NULL UNIQUE,     LastName varchar(255) NOT NULL,     FirstName varchar(255),     Age int );

MySQL:

CREATE TABLE Persons (     ID int NOT NULL,     LastName varchar(255) NOT NULL,     FirstName varchar(255),     Age int,     UNIQUE (ID) );

Чтобы назвать уникальное ограничение и определить уникальное ограничение для нескольких столбцов, используйте следующий синтаксис SQL:

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons (     ID int NOT NULL,     LastName varchar(255) NOT NULL,     FirstName varchar(255),     Age int,     CONSTRAINT UC_Person UNIQUE (ID,LastName) );

SQL Server Функции расширенния

Функция Описание
CAST Преобразует значение (любого типа) в указанный тип данных
COALESCE Возвращает первое ненулевое значение в списке
CONVERT Преобразует значение (любого типа) в указанный тип данных
CURRENT_USER Возвращает имя текущего пользователя в базе данных SQL Server
IIF Возвращает значение, если условие TRUE, или другое значение, если условие FALSE
ISNULL Возвращает указанное значение, если выражение равно NULL, в противном случае возвращает выражение
ISNUMERIC Проверяет, является ли выражение числовым
NULLIF Возвращает NULL, если два выражения равны
SESSION_USER Возвращает имя текущего пользователя в базе данных SQL Server
SESSIONPROPERTY Возвращает настройки сеанса для указанного параметра
SYSTEM_USER Возвращает имя входа для текущего пользователя
USER_NAME Возвращает имя пользователя базы данных на основе указанного id

❮ Назад Вперед ❯

SQL ORDER BY clause examples

We will use the table in the sample database for the demonstration.

1) Using SQL clause to sort values in one column example

The following statement retrieves the employee id, first name, last name, hire date, and salary from the table:


It seems that the rows appear as they are stored in the   table. To sort employees by first names alphabetically, you add an clause to query as follows:

The result set now is sorted by the column.

2) Using SQL  clause to sort values in multiple columns example

To sort by the employees by the first name in ascending order and the last name in descending order, you use the following statement:

First, the database system sorts the result set by the first name in ascending order, then it sorts the sorted result set by the last name in descending order. Notice the change in position of two employees: and

3) Using SQL clause to sort values in a numeric column example

SQL allows you to sort data alphabetically as shown in the previous example and also sort data numerically. For example, the following statement selects employee data and sorts the result set by salary in the descending order:

4) Using SQL to sort dates example

Besides the character and numeric, SQL allows you to sort the result set by date. The following statement sorts the employees by values in the column in the ascending order.

To view the latest employees who have just joined the company, you sort the employees by the hire dates in the descending order as shown in the following statement:

In this tutorial, you have learned how to use the SQL clause to sort the result set based on one or more columns in the ascending or descending order.

  • Was this tutorial helpful ?

MySQL Order By Ascending

To sort data in ascending order, we have to use Order By statement, followed by the ASC keyword. The following are the list of ways to sort data in ascending order.

For example, as a Sales Manager, If you want to identify the low performing products (Products with No Sales, or fewer sales), then write the query as:

By seeing the result, you can understand that x is not performing well, and y has no sales at all. Using this data, you can try different strategies to improve sales.

MySQL Sort in Ascending Order Example

In this MySQL example, we are going to sort the Data in Ascending Order using Yearly Income. Please replace yearly income with any of your required columns in a Table.

From the below screenshot, you can see the Data was sorted by Yearly Income in Ascending order.

MySQL Sort in Ascending Order without using ASC

The ASC keyword is the default keyword in Order By statement, that’s why it is optional to use ASC.

In this example, we are going to sort the customer’s data by First_Name in ascending order without using ASC Keyword.


OUTPUT

MySQL Sort Numeric Position in Ascending Order

In this MySQL Order By ASC example, We are going to sort customers table in Ascending Order using Numerical Position of a Column Name.

The Numerical position of Last_Name is 2. So, data sorted by this column.

MySQL Order By Multiple Columns in Ascending Order

In this MySQL Order By ASC example, we are sorting the Data using multiple columns.

First, data sorted in Ascending Order by First Name. The Last Name then sorts it in Ascending Order.

MySQL Sort By ASC using Alias Column

In this MySQL Order by ASC example, we are going to sort the customer’s Data in Ascending Order using Alias Column Name.

We used the concat string function to concat the First Name, and Last_Name columns to create Name (an Alias Name). Next, we used the Alias name in the ORDER BY Clause. It means customer’s data sort by Name in Ascending Order.

SQL Server ORDER BY clause example

We will use the table in the sample database from the demonstration.

A) Sort a result set by one column in ascending order

The following statement sorts the customer list by the first name in ascending order:

In this example, because we did not specify or , the clause used by default.

B) Sort a result set by one column in descending order

The following statement sorts the customer list by the first name in descending order.

In this example, because we specified the explicitly, the clause sorted the result set by values in the column in the descending order.

C) Sort a result set by multiple columns

The following statement retrieves the first name, last name, and city of the customers. It sorts the customer list by the city first and then by the first name.

D) Sort a result set by multiple columns and different orders

The following statement sorts the customers by the city in descending order and the sort the sorted result set by the first name in ascending order.

E) Sort a result set by a column that is not in the select list

It is possible to sort the result set by a column that does not appear on the select list. For example, the following statement sorts the customer by the state even though the column does not appear on the select list.

Note that the column is defined in the   table. If it was not, then you would have an invalid query.

F) Sort a result set by an expression

The function returns the number of characters of a string. The following statement uses the function in the clause to retrieve a customer list sorted by the length of the first name.

G) Sort by ordinal positions of columns

SQL Server allows you to sort the result set based on the ordinal positions of columns that appear in the select list.

The following statement sorts the customers by first name and last name. But instead of specifying the column names explicitly, it uses the ordinal positions of the columns:

In this example, 1 means the column and 2 means the column.

Using the ordinal positions of columns in the clause is considered as bad programming practice for a couple of reasons.

  • First, the columns in a table don’t have ordinal positions and need to be referenced by name.
  • Second, when you modify the select list, you may forget to make the corresponding changes in the clause.

Therefore, it is a good practice to always specify the column names explicitly in the clause.

In this tutorial, you have learned how to use the SQL Server clause to sort a result set by columns in ascending or descending order.

Example — Sorting Results in Ascending Order

To sort your results in ascending order, you can specify the ASC attribute. If no value (ASC or DESC) is provided after a field in the ORDER BY clause, the sort order will default to ascending order. Let’s explore this further.

In this example, we have a table called customers with the following data:

customer_id last_name first_name favorite_website
4000 Jackson Joe techonthenet.com
5000 Smith Jane digminecraft.com
6000 Ferguson Samantha bigactivities.com
7000 Reynolds Allen checkyourmath.com
8000 Anderson Paige NULL
9000 Johnson Derek techonthenet.com

Enter the following SQL statement:

Try It

SELECT *
FROM customers
ORDER BY last_name;

There will be 6 records selected. These are the results that you should see:

customer_id last_name first_name favorite_website
8000 Anderson Paige NULL
6000 Ferguson Samantha bigactivities.com
4000 Jackson Joe techonthenet.com
9000 Johnson Derek techonthenet.com
7000 Reynolds Allen checkyourmath.com
5000 Smith Jane digminecraft.com

This example would return all records from the customers sorted by the last_name field in ascending order and would be equivalent to the following SQL ORDER BY clause:

Try It

SELECT *
FROM customers
ORDER BY last_name ASC;

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 Краткий справочник

Introduction to SQL ORDER BY clause

When you use the statement to query data from a table, the order which rows appear in the result set may not be what you expected.

In some cases, the rows that appear in the result set are in the order that they are stored in the table physically. However, in case the query optimizer uses an index to process the query, the rows will appear as they are stored in the index key order. For this reason, the order of rows in the result set is undetermined or unpredictable.

The query optimizer is a built-in software component in the database system that determines the most efficient way for an SQL statement to query the requested data.

To specify exactly the order of rows in the result set, you add use an clause in the statement as follows:

In this syntax, the clause appears after the clause. In case the statement contains a clause, the clause must appear after the clause.

To sort the result set, you specify the column in which you want to sort and the kind of the sort order:

  • Ascending ( )
  • Descending ( )

If you don’t specify the sort order, the database system typically sorts the result set in ascending order ( ) by default.

When you include more than one column in the clause, the database system first sorts the result set based on the first column and then sort the sorted result set based on the second column, and so on.

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.

Параметры оператора

 — используется для определения, какие строки должны быть выбраны или включены в GROUP BY.

GROUP BY

 — необязательный параметр операторa , для группировки строк по результатам агрегатных функций (MAX, SUM, AVG, …).

Необходимо, чтобы в SELECT были заданы только требуемые в выходном потоке столбцы, перечисленные в GROUP BY и/или агрегированные значения. Распространённая ошибка — указание в SELECT столбца, пропущенного в GROUP BY.

HAVING

 — необязательный (опциональный) параметр оператора для указания условия на результат агрегатных функций (MAX, SUM, AVG, …).

аналогичен за исключением того, что строки отбираются не по значениям столбцов, а строятся из значений столбцов, указанных в , и значений агрегатных функций, вычисленных для каждой группы, образованной GROUP BY.

Необходимо, чтобы в SELECT были заданы только требуемые в выходном потоке столбцы, перечисленные в GROUP BY и/или агрегированные значения. Распространённая ошибка — указание в SELECT столбца, пропущенного в GROUP BY.

Если параметр GROUP BY в SELECT не задан, HAVING применяется к «группе» всех строк таблицы, полностью дублируя (допускается не во всех реализациях стандарта SQL).

ORDER BY

 — необязательный (опциональный) параметр операторов и , который означает что операторы , возвращают набор строк, отсортированных по значениям одного или более столбцов. Его можно применять как к числовым столбцам, так и к строковым. В последнем случае, сортировка будет происходить по алфавиту.

Использование предложения является единственным способом отсортировать результирующий набор строк. Без этого предложения СУБД может вернуть строки в любом порядке. Если упорядочение необходимо, должен присутствовать в , .

Сортировка может производиться как по возрастанию, так и по убыванию значений.

  • Параметр (по умолчанию) устанавливает порядок сортировки по возрастанию, от меньших значений к большим.
  • Параметр устанавливает порядок сортировки по убыванию, от больших значений к меньшим.

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 Краткий справочник


С этим читают