When working with databases, it is often necessary to display the query result in a certain order, for example, alphabetically. To do this, the DBMS has a special function in SQL - sorting. In this case, the programmer can choose which fields and in what order it will occur in order to achieve the desired result, without applying serious programming skills.
What is sorting in a database?
Working with databases is constantly associated with a large amount of information, which must determine the order. Currently, there are a number of DBMSs with 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.
Ranking the data allows you to simplify the search process, and in some cases helps to solve some problems or optimize the program. SQL sorting is performed according to a separately selected field, and if necessary, if there are identical values in the elements of this field, you can specify additional parameters that determine the location of the rows.
Sort command
SQL sorting in the database is provided by using the ORDER BY function. So, when outputting information from the database, after specifying the columns and tables from which it will be read, the query must specify the sort command, and then determine the field or fields by which sorting will be performed.
For example, if you need to get data from the Name and Age fields from the People table, while displaying the result in alphabetical order by the Name column, this will help to make the following query: SELECT Name, Age FROM People ORDER BY Name.
How to set the sort order?
Modern conditions pose different tasks for programmers, and sometimes it is necessary to predetermine in which order the result will be displayed - descending or ascending, alphabetically or in reverse order? And for this, in the SQL language, the sort order is determined by adding a keyword to the query. After selecting the fields and tables from which the desired information will be obtained, you must add ORDER BY, and then specify the name of the column by which to sort.
To get the reverse order, you must specify the DESC parameter after the name. If it is necessary to arrange the elements according to two or more criteria, then the columns are indicated by a comma, and the priority in ranking will be given to the field that comes first in the list. It is worth noting that arranging the elements in the reverse order, the DESC parameter provides for only one field, after the name of which this keyword is indicated, so if necessary, it must be specified in all selected columns.
Alternative sorting methods
If it is not possible to use the built-in SQL sort function, you can write one of the known algorithms. If you need to achieve the fastest ordering of elements, you should use the "Quick Sort" method , based on half-breaking the array of elements. “Bubble” ranking methods are also very popular, in which two adjacent elements are swapped in case of an incorrect arrangement, “pyramidal sorting” that sends the largest element to the end of the list, as well as “insertion sorting”, which determines the location of each element in turn.
Writing the algorithm yourself will not significantly increase the speed of sorting, however, it will contribute to the development of programming skills, and will also allow you to modify the process by adjusting the ranking scheme for a specific database to increase the efficiency of the program.