Оконные функции в sql

Ограничение возвращаемых строк

Согласно ISO SQL:2003 возвращаемый набор данных может быть ограничен с помощью:

• курсоров, или
• введением оконных функций в оператор SELECT

Оконная функция ROW_NUMBER()

Существуют различные оконные функции. может быть использована для простого ограничения числа возвращаемых строк. Например, для возврата не более десяти строк:

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
) AS foo
WHERE rownumber <= 10

ROW_NUMBER может быть недетерминированным: если key не уникален, каждый раз при выполнении запроса возможно присвоение разных номеров строкам, у которых key совпадает. Когда key уникален, каждая строка будет всегда получать уникальный номер строки.

Оконная функция RANK()

Функция работает почти так же, как ROW_NUMBER, но может вернуть более чем n строк при определённых условиях. Например, для получения top-10 самых молодых людей:

SELECT * FROM (
  SELECT
    RANK() OVER (ORDER BY age ASC) AS ranking,
    person_id,
    person_name,
    age
  FROM person
) AS foo
WHERE ranking <= 10

Данный код может вернуть более чем 10 строк. Например, если есть два человека с одинаковым возрастом, он вернёт 11 строк.

Нестандартный синтаксис

Не все СУБД поддерживают вышеуказанные оконные функции. При этом многие имеют нестандартный синтаксис для решения тех же задач. Ниже представлены варианты простого ограничения выборки для различных СУБД:

SELECT * FROM T FETCH FIRST 10 ROWS ONLY

SELECT FIRST 10 * FROM T

SELECT FIRST 10 * FROM T

SELECT * FROM T ROWS 10

SELECT TOP 10 PERCENT * FROM T ORDER BY col

SELECT * FROM T LIMIT 10

SELECT * FROM T LIMIT 10

SELECT * FROM T LIMIT 10

SELECT * FROM T WHERE ROWNUM <= 10

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:

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:

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 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.

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 Комментарии

Select and Order Data With MySQLi

The following example selects the id, firstname and lastname columns from the MyGuests
table. The records will be ordered by the lastname column:

Example (MySQLi Object-oriented)

<?php$servername = «localhost»;$username = «username»;$password = «password»;$dbname = «myDB»;// Create connection$conn = new mysqli($servername, $username, $password, $dbname);// Check connectionif ($conn->connect_error) {
  die(«Connection failed: » . $conn->connect_error);
} $sql = «SELECT id, firstname, lastname FROM MyGuests ORDER BY lastname»;$result = $conn->query($sql);if ($result->num_rows > 0) {  // output data of each row  while($row = $result->fetch_assoc()) {
    echo «id: » . $row. » — Name: » . $row. » » . $row. «<br>»;
  }} else {  echo «0 results»;}
$conn->close();
?>

Code lines to explain from the example above:

First, we set up the SQL query that selects the id, firstname and lastname columns from the MyGuests
table. The records will be ordered by the lastname column. The next line of code runs the query and puts the resulting data into a
variable called $result.

Then, the checks if there are more than zero
rows returned.

If there are more than zero rows returned, the
function puts all the results into an associative array that we can loop
through. The loop loops through the result set and outputs the data from
the id, firstname and lastname columns.

The following example shows the same as the example above, in the MySQLi procedural way:

Example (MySQLi Procedural)

<?php$servername = «localhost»;$username = «username»;$password = «password»;$dbname = «myDB»;// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
  die(«Connection failed: » . mysqli_connect_error());}$sql = «SELECT id, firstname, lastname FROM MyGuests
ORDER BY lastname»;$result = mysqli_query($conn, $sql);if (mysqli_num_rows($result) > 0) {
  // output data of each row  while($row = mysqli_fetch_assoc($result)) {    echo «id: » . $row. » — Name: » . $row. » » . $row. «<br>»;
  }} else {  echo «0 results»;}mysqli_close($conn);
?>

You can also put the result in an HTML table:

Example (MySQLi Object-oriented)

<?php$servername = «localhost»;$username = «username»;$password = «password»;$dbname = «myDB»;// Create connection$conn = new mysqli($servername, $username, $password, $dbname);// Check connectionif ($conn->connect_error) {
  die(«Connection failed: » . $conn->connect_error);
} $sql = «SELECT id, firstname, lastname FROM MyGuests ORDER BY lastname»;$result = $conn->query($sql);if ($result->num_rows > 0) {  echo «<table><tr><th>ID</th><th>Name</th></tr>»;
  // output data of each row  while($row = $result->fetch_assoc()) {    echo «<tr><td>».$row.»</td><td>».$row.» «.$row.»</td></tr>»;
  }  echo «</table>»;} else {  echo «0 results»;}
$conn->close();
?>