Often there is a need to output the result of the query in a certain order, for example, according to the alphabet. To do this, in the DBMS there is a special feature in SQL language - sorting. In this case, the programmer can choose which fields and in what order it will happen to achieve the desired result, without applying serious programming skills.
What is sorting in the database?
Working with databases is constantly related to the large amount of information to which it is necessary to determine the order. Currently, there are a number of DBMS, which has a wide range of functions, the most popular of which are Oracle and MS SQL. Sorting information as one of the main procedures in working with the database is provided by a special built-in function in each of them.
Data ranking allows you to simplify the search process, as well as in some cases helps to solve some tasks or optimize the operation of the program. SQL sorting is performed according to a separately selected field, and if necessary, if you have the same values \u200b\u200bin the elements of this field, you can specify additional parameters that determine the location of the rows.
Sort team
SQL sorting in the database is provided by using the ORDER BY function. So, when displaying information from the database, after specifying columns and tables of which will be read, you must specify the sort command, after which it is necessary to determine the field or fields by which sorting will be made.
For example, if you need to receive data from the Name and Age fields from the PEOPLE table, while disrupting the result in alphabetical order via the Name column, it will help to make the following query: Select Name, Age From People Order by Name.
How to set the sort order?
Modern conditions set various tasks before programmers, and sometimes it is necessary to predetermine, in what order will the result be derived - descending or ascending, alphabetically or in reverse order? And for this, in the SQL language, the sorting order is determined by adding a keyword to the request. After selecting the fields and tables, from which the desired information will be received, you must add Order by, after which you specify the name of the column for which it is necessary to sort.
To obtain the reverse order, you need to specify the DESC parameter after the name. If you streamline items are necessary on two or more criteria, the columns are indicated by comma, and the priority in the ranking will be given to the field that goes on the first list. It is worth noting that building items in the reverse order The DESC parameter provides for only one field, after which this keyword is specified, so if necessary, you need to specify in all selected columns.
Alternative sorting methods
In the absence of the ability to use the built-in SQL sorting function, you can write one of the known algorithms. If it is necessary to achieve the rapid ordering of the elements, it is necessary to apply the method based on haunting array of elements. Methods of ranking "bubble" also possess great popularity, in which two adjacent elements are changing in places in the event of an incorrect location, "pyramidal sorting", sending the largest element to the end of the list, as well as "sorting inserts", which predetermining the location of each element in turn.
The personally writing of the algorithm will not significantly increase the sorting speed, however, contributes to the development of programming skills, and will also allow to modify the process by setting up a ranking scheme for a specific database to improve the efficiency of the program.
ORDER BY Offer in Access sorts records returned by the request, ascending or descending the values \u200b\u200bof the specified field (fields).
Syntax
SELECT list_Pole
From. table
Where conditions_Tealth
[, field2. ][, ...]]]
Select Instructions containing ORDER BY proposal includes the following items:
Remarks
Order by ORDER BY is not mandatory. It should be used when it is necessary to display data in sorted form.
By default, the order of sorting ascending is applied (from A to Z, from 0 to 9). In the two examples below, it is shown to sort the names of employees by last name.
SELECT LASTNAME, FIRSTNAME
From Employees.
Order by LastName;
SELECT LASTNAME, FIRSTNAME
From Employees.
Order by Lastname ASC;
To sort descending (from I up to A, from 9 to 0), add a reserved DESC word at the end of each field to which you want to sort the records. In the example below, sorting the names of employees in descending order of wages is performed.
SELECT LASTNAME, SALARY
From Employees.
Order by Salary Desc, LastName;
If in the Order By sentence, specify a field containing data of MEMO type or OLE objects, an error will occur. The Microsoft Access DBMS core does not support sorting through the fields of these types.
ORDER BY proposal is usually the last element in the SQL instruction.
The ORDER BA offer you can include additional fields. First, the records are sorted by the field indicated in the Order By the first sentence. Then, for entries with the same values \u200b\u200bof the first field, sorting over the field indicated by the second, etc.
Next cycle:
Step 8. Simple sorting
If the result of your SQL query should be the source material of a certain report, the question of sorting data in it becomes extremely important, since a person reading the report undersized properly is very difficult to quickly find in it the necessary part of his information. To sort data on the query result columns (in our case, on the fields of the table), the Keyword of ORDER BY is used in SQL. An example of the simplest sorting is shown below. A request from step 2 is taken as the basis: "Request with a simple selection criterion." We sort employees by the field s_name (Full name).
SELECT S_NAME, S_EXPERIENCE FROM D_STAFF WHERE S_EXPERIENCE
Sort the result SQL query one field.
Step 9. Complex sorting
Often, and even almost always it is necessary to sort the data by more than one column and is not always in ascending order. SQL syntax suggests after the Keyword order by the list of columns separated by the semicolon, as well as the sorting method for each column: in ascending order of values \u200b\u200b- ASC or the order of their decrease - DESC. In the example below, we show records already about all employees in descending order of their experience. Employees with the same experience we sort in alphabetical order.
SELECT S_EXPERIENCE, S_NAME FROM D_STAFF ORDER BY S_EXPERIENCE DESC, S_NAME ASC
Sort the result of SQL query in two fields.
Quite often, the reverse order of sorting is used with columns of type [DATE]. If the date stores, for example, the date of the information of the information, then when reverse sorting, at the very beginning of the list, there are those records that have been added recently relative to the rest. In the event that the request retrieves the news announcements from the database, we get a list of announcements sorted in descending order of their relevance, which can be extremely useful because you are usually reading the announcements from top to bottom, and not everyone shows them on news sites, but only a few The most "fresh".
In the future, we may need to sort our sample - in alphabetical order for text or ascending / descending - for digital values. For such purposes in SQL There is a special operator Order by. .
1. Sort the selected data.
Let's keep all our table by sort the amount of product sales, namely on the column Amount..
SELECT * From SumProduct Order by Amount
We see that the request has unorded records ascending in the field Amount.. Be sure to follow the sequence of operators, i.e. operator Order by. Must go at the very end of the request. Otherwise, an error message will be received.
Also feature of the operator Order by. It is that it can sort the data on the field that we did not choose in the request, that is, it is enough for it to be in the database.
2. Sort by multiple fields.
Now I will connect our example additionally in another field. Let it be a field City.which displays the place of sale of products.
SELECT * From SumProduct Order by Amount, City
The sorting order will depend on the order of field location in the query. That is, in our case, first the data will be sorted by column Amount.and then City..
3. Direction of sorting.
Despite the fact that the default operator Order by. Sorts ascending, we can also register sorting values \u200b\u200bdescending. To do this, at the end of each field, we put the operator DESC. (which is a reduction from the word Descending).
SELECT * From SumProduct Order by Amount Desc, City
In this example, the value in the field Amount. were dismissed, and in the field City. - Ascending. Operator DESC. It is applied only for one column, so if necessary, it must be prescribed after each field that takes part in the sorting.
