Sorting sql server. Working with the database
Next cycle:
Step 8: Simple sorting
If the result of your SQL query is to become the source material for a report, then the issue of sorting the data in it becomes extremely important, since it is very difficult for a person reading a report that is not properly sorted to quickly find the piece of information they need. To sort data by query result columns (in our case, by table fields), SQL uses the ORDER BY keyword. An example of the simplest sorting is given below. The basis is taken from the request from step 2: “Request with a simple selection criterion.” We sort employees by the S_NAME field (full name).
SELECT S_NAME, S_EXPERIENCE FROM D_STAFF WHERE S_EXPERIENCE
Sorting the result of an SQL query by one field.
Step 9. Complex sorting
It is often, and even almost always, necessary to sort data by more than one column and not always in ascending order. The SQL syntax assumes that after the ORDER BY keyword, a list of columns separated by a comma, as well as a sorting method for each column: in ascending order of values - ASC or in descending order - DESC. In the example below, we show records for all employees in descending order of their length of service. We sort employees with the same length of service in alphabetical order.
SELECT S_EXPERIENCE, S_NAME FROM D_STAFF ORDER BY S_EXPERIENCE DESC, S_NAME ASC
Sorting the result of an SQL query by two fields.
Quite often, the reverse sort order is used with columns like [date]. If the date stores, for example, the date the information was entered, then when reverse sorting, those records that were added recently relative to the rest appear at the very beginning of the list. If the query retrieves news announcements from the database, then we receive a list of announcements sorted in descending order of their relevance, which can be extremely useful, since announcements are usually read from top to bottom, and on news sites they are not shown all, but only a few the most “fresh”.
The ORDER BY clause in Access sorts the records returned by a query in ascending or descending order of the values of the specified field(s).
Syntax
SELECT field_list
FROM table
WHERE selection_condition
[, field2 ][, ...]]]
A SELECT statement containing an ORDER BY clause includes the following elements:
Notes
The ORDER BY clause is optional. It should be used when you need to display data in sorted form.
The default sort order is ascending (A to Z, 0 to 9). The two examples below show sorting employee names by last name.
SELECT LastName, FirstName
FROM Employees
ORDER BY LastName;
SELECT LastName, FirstName
FROM Employees
ORDER BY LastName ASC;
To sort in descending order (Z to A, 9 to 0), add the reserved word DESC at the end of each field by which you want to sort the records. The example below sorts employee names in descending order of salary.
SELECT LastName, Salary
FROM Employees
ORDER BY Salary DESC, LastName;
If you specify a field that contains Memo data or OLE objects in the ORDER BY clause, an error will occur. The Microsoft Access database engine does not support sorting by these types of fields.
The ORDER BY clause is usually the last element in an SQL statement.
You can include additional fields in the ORDER BY clause. Records are first sorted by the field specified first in the ORDER BY clause. Records with the same first field values are then sorted by the second field specified, and so on.
In the future, we may need to sort our selection - alphabetically for text or ascending/descending for numeric values. For such purposes in SQL there is a special operator ORDER BY .
1. Sorting the selected data.
Let's sort our entire table by the amount of product sales, namely by the column Amount.
SELECT * FROM Sumproduct ORDER BY Amount
We see that the query sorted the records in ascending order in the field Amount. It is imperative to follow the sequence of operators, i.e. operator ORDER BY should go at the very end of the request. Otherwise you will receive an error message.
Also a feature of the operator ORDER BY is that it can sort data by a field that we did not select in the query, that is, it is enough that it is in the database at all.
2. Sorting by several fields.
Now let's sort our example additionally by one more 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 the fields in the request. That is, in our case, first the data will be sorted by column Amount, and then by City.
3. Sorting direction.
Even though the default operator ORDER BY sorts in ascending order, we can also specify sorting values in descending order. To do this, at the end of each field we put the operator DESC (which is an abbreviation for the word DESCENDING).
SELECT * FROM Sumproduct ORDER BY Amount DESC , City
In this example, the value in the field Amount were sorted in descending order, and in the field City- Ascending. Operator DESC applies only to one column, so if necessary, it must be written after each field that takes part in the sorting.