Main page
Numbering
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:
No
model
1
1121
2
1232
3
1233
4
1260
5
1276
6
1288
7
1298
8
1321
9
1401
10
1408
11
1433
12
1434
13
1750
14
1752
15
2111
16
2112
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:
(2)
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
ORDER BY 1
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