Books and articles about SQL Rambler's Top100 Сменить язык на: Русский 23 October 2017 07:01:34
Skip Navigation Links  


Print  Print version

Main page


S. Moiseenko

Usually the necessity of the numbering of records occurs in forming of reports. In this case the numbering of strings returned by a query they usually realize on a client side. For example, it's easy to renumber the strings of report, prepared in MS Access. However sometimes there's the necessity to make it in this right query. We'll examine this problem now.

Numbering of strings in accordance with the order of values of a primary key.

Of course, strings should be numbered in accordance with some order. Let this order to be set by the primary key field, that is in order of increase (or decrease) of values in this field. Let us suppose for distinctness that we must renumber models in the table Product, where the model number is a primary key. The important thing is that values of primary key can't contain duplicates and NULL-values, because of which there's a principled possibility to set one-to-one accordance between the number of the model and number of the string in order of sorting the models.

First, let's consider the following query:

SELECT P1.model, P2.model FROM Product P1 JOIN Product P2 ON P1.model <= P2.model

Here the joining of two identical tables on the inequality P1.model <= P2.model is performing, consequently each model from the second table (P2.model) will join only with the models from the first table (P1.model), numbers of which are less or equal the number of this model. Consequently we'll get that the model with minimal number (1121) will be present in the second row of resulting set only once, as it less or equal only to itself . On the other end will be the model with maximal number, as any model is less or equal to it. Hence, the model with maximal number will match with every model, and number of such matches is equal to overall number of models in the table Product.

It's clearly that this number of each model occurring in the second row of resulting set is exactly an ordinal number of the model in sorting models ascending.

Thus, to solve our task of numbering it's enough to count models in the right row, that is easily can be done by grouping and using aggregate function COUNT:

(1) SELECT COUNT( *) no,P2.model FROM Product P1 JOIN Product P2
    ON P1.model <= P2.model GROUP BY P2.model

Here's the result of performance of this query:



































In order to number in inverse order it's enough to change the sign of inequality to the opposite.

Numbering strings when there are duplicates in the results column.

According to the relational theory a table can't have identical rows. And though realizations allow creating tables without a primary key and as a result allow identical rows this sort of situation shows a mistake in planning. Also, a table without a primary key or unique index is not renewable. The last conclusion is quite reasonable because the system has no information as to which of the duplicates to choose.

So, speaking of duplicates, we mean duplicates in the resulting set whose appearance can be caused by the complete or partial absence (in the case of a compound key) of the primary key in the resulting set.

To illustrate, let's examine the following query

SELECT id_psg FROM pass_in_trip

which will return the numbers of passengers who took a flight. As one passenger can take several flights we get duplicates here. But this passenger can't take the same flight on the same day more than once and that is regulated by the appropriate primary key - {trip_no, date, id_psg}.

So we need to renumerate the passengers who may duplicate. Let's first define the order in which it should be done. Let this order be based on three fields – flight date, passenger identifier and the flight number (in ascending order).

To reduce this task to the one we've discussed previously (this is possible because three enumerated fields form the primary key) let's construct a column that would unite information from the listed fields. As the fields have different data types we'll convert them to a symbolical representation and perform concatenation.

For this we need to define the number of characters. There is no time mentioned in the flight date so 11 symbols are enough. The flight number is a 4-character number everywhere. Only the passenger identifier remains. Based on the database we have, we'll use 2 symbols -- this does not in any way detriment from the general approach. However, for the sorting to be performed correctly, we need to add a zero before the actual number for the single-digit passengers, like 01, 09, etc. Otherwise, passenger number 2 will be placed after passenger number 10, for example. Let's perform the appropriate conversions:

Date - CAST( date AS CHAR(11))
Flight number - CAST(trip_no AS CHAR(4))
Passenger identifier - RIGHT('00' + CAST(id_psg AS VARCHAR(2)), 2).

In the last conversion (2) I used the non-standard RIGHT function (SQL Server), which substracts from a string a defined number of symbols starting from the right. Of course the SUBSTRING function could be used but this one is shorter and, besides, other commercial products should have analogous “improvements” on the standard. Combining these expressions in the indicated order we get a unique column which will be used to numerate the passengers according to increment (decrement) of values in this column. Using this column we can utilize solution (1) (see issue #5).
Our final result will be

SELECT COUNT(*) num, P2.id_psg FROM (
SELECT *, CAST(date AS CHAR(11)) + RIGHT('00' + CAST(id_psg AS VARCHAR(2)), 2)+
CAST(trip_no AS CHAR(4)) dit FROM pass_in_trip) P1 JOIN (
SELECT *, CAST(date AS CHAR(11)) + RIGHT('00' + CAST(id_psg AS VARCHAR(2)), 2)+
CAST(trip_no AS CHAR(4)) dit FROM pass_in_trip) P2
ON P1.dit <= P2.dit
GROUP BY P2.dit, P2.id_psg

For numbering in a different order just concatenate the transformations (2) in a different sequence. For example, to number passengers in the sequence of their identification numbers the first item should be RIGHT( '00' + CAST(id_psg AS VARCHAR(2)), 2).

Given examples here can be done directly on the website by selecting the check box “Without checking” on the page with SELECT exercises.

Main page

Print  Print version

Usage of any materials of this site is possible
only under condition of mandatory allocation of the direct link to a site
on each page where used materials are placed.

 Main   Articles    Books 
Рейтинг Rambler's Top100 Alt Упражнения по SQL: обучение, тестирование, сертификация по языку SQL Copyright c 2002-2006. All rights reserved.