Using the distinct construct is relevant not only for selecting unique entries. This is a good way to test the application. The main semantic load on the select distinct MySQL query keyword is to select only those records in which the specified field has a unique value.
You can use multiple fields in a single query. You can combine fields with MySQL functions, apply additional conditions in the selection and sorting. Using the group by construct is also acceptable, but the uncertainties that arise from this must be taken into account.
Basic Provisions and Syntax
Testing and optimization: select distinct is one of the most popular ways to achieve what you want. A case in point is a table containing 99,999 records, which are randomly generated from three arrays:
The ex_workers test table is generated using the PHP rand (0,10) function. Each array has exactly 11 elements. Naturally, a uniformly distributed random variable on the number of records 99,999 cannot allow at least one absence of first_name on all last_name and vice versa.
Of course, the probability remains, but for each unique name in the $ aWorkersF array, most likely there will be exactly eleven variants of $ aWorkersL. In this case, in order to test doubts about the initial data set, as well as its multiple generation - a good solution. Ideal data sets are not the best means of checking algorithms.
The results of the execution of queries (1) and (2) - the first and second columns - show that indeed for each unique value of first_name there are eleven last_name values and vice versa. At the same time, the rightmost column is longer than eleven lines and has 121 lines - with query (3) and query (4).
Enumerating the fields distinct `first_name`,` last_name` is equivalent to distinct concat (`first_name`,` last_name`). However, this is not a general case, but a particular solution.
The main point of MySQL select c distinct query
Common practice: tables always have duplicate fields. Without this significant circumstance, relational databases simply do not exist . In the example, the table contains the key field i_status and its meaning is w_status. This is the position held by the employee.
Ideally, the fields i_status and w_status should be arranged in a separate table, and in the ex_workers table only the key field i_status should remain, by which you can always get the name of the position. Here such a solution is given as an example.
By running the select distinct query on these two fields, you can get all the necessary data to form the selector on the web page. Such a selector allows you to select the displayed employees for the positions they occupy. This may not be a selector, but the head of the table or the axis of the graph of statistics on the demand for workers in a particular specialty.
This is not to say that the main reason for choosing unique records is exclusively selectors, table headers or coordinate axes, but this is an extremely common purpose and use of the select distinct construct.
Uniqueness on dynamic data
Information processing is in many ways the search for the right decision to make. The analysis of conditions can be carried out by means of PHP or another programming language, but, as a rule, all the primary information is immediately written to the database tables. This is no longer a tradition, but a normal memory for a modern site. Memory in a relational database format is an excellent and practical solution.
The select distinct design has an extensive scope - the adoption of unconditional decisions, if you design the creation of dynamic tables during the operation of the site, for example:
- parsing pages;
- tracking visitor behavior;
- forming a security perimeter by analyzing deviations from the predefined (permissible) nature of the actions of employees.
When planning simple algorithms for analyzing incoming information, writing the primary result into a table, you can always have an adequate solution for a simple select distinct query.
The simplicity of tables and the essence of relational logic
Once programming and creating databases resembled the construction of railways and guest houses. Basically, this version of progress in the scientific and technological sector was the prerogative of the socialist economy, but Silicon Valley also managed to excel in this matter.
Today, the simpler the better. Less and less frequently, questions began to arise about how to make select distinct on a dozen fields from several tables at the same time. Skilled development began to relate to the creation of databases from an extremely simple point of view: if one developer made a database or a query on a table, then another developer should understand what was done in short.
If you need to make at least some noticeable mental or temporary efforts to understand the organization of data and the selection of information, this is an objective reason to review the results of the work done.