SpatiaLite Cookbook Chapter 01: Kitchen tools and cooking techniques

Very simply stated:

• any computer really only a stupid machine built out of a messy bunch of silicon
• can add 1+1 with the result of 10
• when told to do so, can make more errors in a few seconds that you could during your lifetime
• absolutely unable to understand text, other that adding (if told to do so) the characters to each other
• a conversion table must exist for the computer to translate its straightforward '0' and '1's, to the, many different, messy, alphabets that it exists in the world

This conversion table is known as as a Charset Encoding.

---

The viewpoint of the computer is:

---

Be it as it may, Charset Encoding.problems are caused by the historical development of text files.
So lets invest the time to collect the needed information to the understand the cause of the problem.

Most Planets, such as Earth, are a sphere ... not exactly, Earth has an ellipsoidal shape (slightly flattened at the poles) ...
oh no, that's absolutely wrong: Earth isn't a geometric regular shape, it actually is a geoid

All the above assertions can be assumed to be true, but at different approximation levels.
Near the Equator differences between a sphere and an ellipsoid are rather slight and quite unnoticeable

but nearer to the Poles such differences becomes greater and most easily appreciable.

For all practical purposes differences between an ellipsoid and a geoid are very similar

but for long range aircraft navigation (or even worse, for satellite positioning),
these differences must be taken into consideration. to receive reliable results.

Independent of the real shape of the Earth, the position of each point on the planet's surface can be precisely determined by simply measuring the two angles: longitude and latitude.
In order to set a complete Spatial Reference System [aka SRS], we could have chosen to use the Poles and the Equator (which after all, exists on all astronomic objects):

as the Prime Meridian of choice, or retain the, purly artificial, Greenwich Meridian, simply because it has been used for such a long time

Any SRS based on longitude/latitude coordinates is known as a Geographic System.
Using a Geographic SRS surely grants you maximum precision and accuracy: but unfortunately causes undesirable side-effects:

• paper sheets (and monitor screens) being flat, they don't look like a sphere at all
• using angles makes measuring distances and areas really difficult and counter-intuitive.y/li>

---

For many centuries cartographers have invented several (conventional) systems enabling to represent spherical surfaces as a flattened plane: none of which are completely satisfactory.

All them introduce some degree of approximation and deformation.
Choosing one or the other is mostly based on the resolution of an aspect of a problem:

A good solution to correctly display a small portion of the Earth,
will be an awful solution when used to represent much bigger area
and vice versa.

We will now go through some of these solutions, explaining the benefits and drawbacks of each.

---

First, the often used, UTM [Universal Transverse Mercator] map projection.

As you can see, this map projection introduces severe and unacceptable deformations the farther you get from the center line (between the North and South Poles)

South America at the left, India and South-East Asia on the right.

But along the center line (Africa and Europe), you can see that UTM allows a near perfect, planar projection of excellent quality.

The area around the center line, depending on the scale of the map, must be very narrow to remain accurate.
The wider the area gets, the stronger and more evident the deformations becomes.

This is why UTM defines 60 standard zones, each of which covers exactly 6 longitude degrees.
Merging two adjacent zones (12 degrees) reduces the accuracy, but is still acceptable for many practical purposes.
Exceeding this limit produces low-quality results, and should be avoided.

---

Attempting to standardize the chaos

During the past two centuries almost every national state has introduced at least one (and very often, more than one) map projection system and related SRS : the overall result has become vary chaotic and diffecult to deal with.

Happily, an international standard has envolved which makes tha handeling map SRS easier.
The European Petroleum Survey Group [EPSG] maintains a huge worldwide dataset with more than 3,700 different entries.
Although many of them are now obsolete, and play only a historical role.
Others are only useful in very limited area.
This impressive collection, for which each single entry is uniquely identified by a numeric ID and descriptive name,
thus avoiding confusion and ambiguity.

Any Spatial DBMS requires some SRID-value to be specified for each Geometry to correctly interpret the positions. Such SRID simply is a Spatial Reference ID, and (most cases) coincides with the corresponding EPSG ID

In order to understand the SRID chaos better, here is a list of SRIDs often used in a (small) country such as Italy:

EPSG SRID	Name	Notes
4326	WGS 84	Geographic [long-lat]; worldwide; used by GPS devices
3003 3004	Monte Mario / Italy zone 1 Monte Mario / Italy zone 2	(Rome 1940) but still commonly used, based on the obsolete 1924 International Ellipsoid
23032 23033	ED50 / UTM zone 32N ED50 / UTM zone 33N	European Datum 1950, also based on the 1924 International Ellipsoid, but not conforming to standard UTM zones
32632 32633	WGS 84 / UTM zone 32N WGS 84 / UTM zone 33N	WGS84, adopting the planar UTM projection, but based upon North America (thus, due to Continental Drift, the two opposite sides of the Atlantic Ocean become more distant, about 1 cm per year)
25832 25833	ETRS89 / UTM zone 32N ETRS89 / UTM zone 33N	enhanced evolution of WGS84: official EU standard, based on the Tectonic Plate of Europe (to which Italy does not belong to)
3035	ETRS89 / LAEA Europe	Single CRS for all Europe. Scales larger than 1:500,000: official EU standard

And the following examples may help to understand even better:

Community	SRID	Coordinates
		X (longitude)	Y (latitude)
Roma	4326	12.483900	41.894740
	3003	1789036.071860	4644043.280244
	23032	789036.071860	4644043.280244
	32632	789022.867800	4643960.982152
	25832	789022.867802	4643960.982036
	3035	4527853.614995	2091351.235257
Milano	4326	9.189510	45.464270
	3003	1514815.861095	5034638.873050
	23032	514815.861095	5034638.873050
	32632	514815.171223	5034544.482565
	25832	514815.171223	5034544.482445
	3035	4257519.706153	2483891.866830

As you can easily notice:

• WGS84 [4326] coordinates are expressed in decimal degrees, because this one is a Geographic System directly based on long-lat angles.
• most systems adopt coordinates expressed in meters: all these are projected aka planar systems.
• Y-values look very similar for every planar SRS: that's not surprising, because this value simply represents the distance from the Equator,
  unless they adopt a false northing, as 3035 does (3210000).
• X-values are more dispersed, because different SRS's adopt different false easting origins: i.e. they place their Prime Meridian in different (conventional) point.
• Anyway, any UTM-based SRS gives very closely related values, simply because all them share the same UTM zone 32 definition.
• The (small) differences you can notice about different UTM-based SRSes can be easily explained: UTM zone 32 is always the same, but the underlying ellipsoid changes each time.
  Getting a precise measure for an ellipsoid's axes isn't an easy task: and obviously over time several increasingly better and more accurate estimates have been progressively adopted.

Distance intercurring between Roma and Milano
SRID	Calculated Distance
4326	4.857422
3003	477243.796305
23032	477243.796305
32632	477226.708868
25832	477226.708866
Great Circle	477109.583358
Geodesic	477245.299993
3035	476622.040725

And now we can examine how using different SRS's affects distances:

• Using WGS84 [4326] geographic, long-lat coordinates we'll actually get a measure corresponding to an angle expressed in decimal degrees. [not so useful, really ...]
• any other SRS will return a distance measure expressed in meters: as you see, the results aren't exactly the same.
• Great Circle distances are calculated assuming that the Earth is exactly a sphere: and this one obviously is the worst estimate we can get.
• on the otherhand Geodesic distances are directly calculated on the reference Ellipsoid.

You may have also noted, that the worst distance (> 600 meters) returned was from 3035 (ETRS89 / LAEA Europe), which is only intended to be used for large areas.
The deformations seen above in the World-Image make itself here, as numbers, noticeable, since the distance being measured is far from the center of the projected area.
That is why the remarks about 'ETRS89 / LAEA Europe' should be carefully read:

Single CRS for all Europe. Used for statistical mapping at all scales and other purposes where true area representation is required.
Use ETRS89 / LCC (code 3034) for conformal mapping at 1:500,000 scale or smaller or ETRS89 / UTM (codes 25828-37 or 3040-49) for conformal mapping at scales larger than 1:500,000.

Conclusion:: Thou shall not have exact measures

But this isn't at all surprising since in physical and natural sciences: any measured value is always affected by errors and approximations.
Also any calculated value will be effected by rounding and truncation artifacts.

Since an absolutely exact result simply doesn't exist in the real world: be aware you will get nothing more than, a more or less an approximated, value.
The goal should be to know how to properly reduce such approximations in the best possible way.

---

---

Summa summarum:
Great care must be taken to correctly chose which projection to use:

based on what the projection was designed for and your needs

How spatialite_gui will look, after submitting a standard SQL-Query:

You can follow two different approaches in order to query a DB table:

1. you can simply use the Query Table menu item.
   1. this the quickest and easiest way and completely user friendly.
   2. you simply have to click the mouse's right button over the required table, for the context menu to be shown.
   3. then you can simply use the lowermost buttons in order to scroll the result-set to the: top/prev/next/end at as you need it.
   4. this approach is rather mechanical, and doesn't allow you to exploit the powerful capabilities of SQL, by refining the result-set as you need it.
   5. Note: the records of the result-set can be changed, since a ROWID exists
2. alternatively you can hand-write any arbitrary SQL statement into the uppermost pane, then pressing the Execute button.
   1. this one is the hardest way: you are responsible for what you are doing (or even misdoing ...)
   2. but this way you can take full profit of the powerful capabilities of SQL, by refining the result-set as you need it.
   3. Note: the records of the result-set cannot be changed, since no ROWID exists

In both cases, only 500 records of the result-set will, at first, be returned.

• Pressing the next or previous button will then retrieve the next or previous 500 records
• Pressing the reset button (in the middle) will retrieve the same 500 records again
• Pressing the first or last button will then retrieve the first or last 500 records

---

How spatialite_gui will look, after opening the BLOB explorer:

BLOB GEOMETRY: will show the size of the stored binary-data sz=nnn
Using the BLOB explorer function, by simply clicking the mouse's right button over the corresponding value, will give you a much richer preview of any Geometry.

---

A quick overview of cities1000:

SELECT
"The 'cities1000' table contains cities of of the world with a population ≥ 1000 ("||
(SELECT count(id_rowid) FROM cities1000)||" records),"||char(10)||" "||
(SELECT count(id_rowid) FROM cities1000 WHERE (country_code = 'IT'))||
" of which are in Italy (country_code = 'IT')" AS message;

The 'cities1000' table contains cities of of the world with a population ≥ 1000 (128743 records),
 9900 of which are in Italy (country_code = 'IT')

• during the import of the data, we set default values invalid/non-set data (elevation = 0)
• CASTING for numeric data was also done to insure that mubers are correctly interpreted
• CASTING for elevation to DOUBLE (supporting mm) which is the most common use for dem-data
• the table was filled with sorting order of: country_code, admin1_code, name

---

You can test how a free-hand SQL works using this SQL statement:

SELECT
 name,
 country_code,
 admin1_code,
 admin2_code,
 admin3_code,
 population,
 elevation,
 longitude,
 latitude,
 geom_wgs84
FROM cities1000
WHERE
( -- will check the 128556 records that fulfills this condition, returning 23 records
 (elevation < 0)
)
ORDER BY elevation DESC;

simply copy the SQL statement, then paste it into the query panel, and finally press the Execute button to create a result-set.

---

With spatialite_gui returning a result-set which will look something like this:

The Sql-Query will list all cities, where the elevation is < 0, sorted with the highest value first.

• will return 23 records, the highest being -1.0 meters, the lowest -60.0 meters

'(elevation < 0)' is called a filter (or WHERE) condition.
A WHERE Statement can contain more that one filter condition.

The first condition will always read/query all of the records (in our case 128556) of the TABLE.

• When more than 1 filter condition is being used
  • the following conditions will only be performed on those records that fulfill the previous conditions.

Therefore the order of the conditions is important.

---

We will now add a new filter condition: (country_code = 'IT') (cities in Italy)

• Question: what is easier to do?
  • Check if the 9900 cities in Italy have an elevation < 0 ?
  or
  • Check if the 23 cities that have an elevation < 0 are in Italy?

I would chose the second option, adaption the WHERE Statement in this way

• since both conditions must be fulfilled, we will add an 'AND' after the first condition

WHERE
( -- will check the 128556 records of the table (first condition)
 (elevation < 0) AND
 -- will check the 23 records that have an elevation < 0
 (country_code = 'IT')
)

The result: 1 record, retrieved in 00:00:00.098 ms [as opposedd to 00:00:00.104]

 Scardovari IT 20 RO 029039 1434 -1.000000 12.454620 44.897650

---

Not satisfied with that, but also want the cities in Iran?

• since one or the other of the second condition must be fulfilled, inclose them inside brackets add an 'OR' between them

WHERE
( -- will check the 128556 records of the table (first condition)
 (elevation < 0) AND
 -- will check the 23 records that have an elevation < 0
 ((country_code = 'IT') OR (country_code = 'IR'))
)

The result: 2 records.

 Scardovari IT 20 RO 029039 1434 -1.000000 12.454620 44.897650
 Ziabar (Gaskar) IR 08 4500 -7.000000 49.245900 37.426100

---

Still not satisfied ?? Because there is too much to type ??

• use a list of conditions for 1 column with 'IN ('IT','IR')'

WHERE
( -- will check the 128556 records of the table (first condition)
 (elevation < 0) AND
 -- will check the 23 records that have an elevation < 0
 (country_code IN ('IT','IR'))
)

with the same result.

---

With that, you have enough to start experiment with.

A proper understanding of the WHERE statement is very important, so familiarizing yourself by quering data for an area of your interest, where you have a good idea of what results should be , would be a good idea.

The following SQL queries are so elementary that you can directly verify the results yourself.

---

Simply follow any example executing the corresponding SQL statement (using copy&paste).

---

This one really is the pons asinorum of SQL:

all columns for each row of the selected table will be dumped following a random order

SELECT
 *
FROM com2011_s;

This is what you use when you have no idea what the table contains.

---

There is no need to retrieve every column:

You can explicitly choose:

• which columns have to be included in the result-set
• establishing the relative column-order within the result set
• (you can also sort and filter the results, more about that later)

SELECT
 pop_2011, comune
FROM com2011_s;

Here, the first column will be 'pop_2011', the second then 'comune' (in the original table it is the other way around)

---

You can also re-name each column, so that the shown column-name (for the user) is more explanitory.

SELECT
 Pro_cOm AS code, COMUNE AS name,
 pop_2011 AS "population (2011)"
FROM com2011_s;

This sample shows two important aspects to must be heeded:

• SQL names are case-insensitive: COMUNE. and comune. refers the same column.
• SQL names cannot contain forbidden characters (such as spaces, brackets, colons, hyphens and so on).
  And they cannot correspond to any reserved keyword (i.e. you cannot define a column named e.g. SELECT or FROM., because they shadow SQL commands)
• 'Cheating' is, of course, possible by explicitly setting a mask for any forbidden name, thus becoming a permissable name.
  

  In order to apply such masking, you simply must enclose the full name within a pair of double quotes.

• you can, obviously, double quote every SQL name: this is completely harmless, but don't ask for extra time for doing so ....
• in the (extremely rare, but not impossible) case that your, corrected, forbidden name already contains one or more double quote characters, then you must insert an extra double quote for each one of them.
  e.g.: A"Bc"D has to be correctly masked as: "A""Bc""D"

---

SQL allows you to order rows into the result-set in almost any form that you need.

Here the Communities will be sorted Alphabetically:

SELECT
 pro_com, comune, pop_2011
FROM com2011_s
ORDER BY comune;

Here the result will first be sorted by the Region id (numeric, descending) and then by the Community names (alphabetically, ascending) :

SELECT
 cod_reg, pro_com, comune, pop_2011
FROM com2011_s
ORDER BY cod_reg DESC, comune ASC;

(Whether this is sensible or not, is is a completely different question)

---

You can order as ASC.ending or as DESC.ending order, as needed.

The ASC. qualifier is usually omitted, since it is the default ordering

• Ascending order means A-Z for text values: and from lesser to greater for numeric values.
• Descending order means Z-A for text values: and from greater to lesser for numeric values.

SELECT
 pro_com, comune, pop_2011
FROM com2011_s
ORDER BY pop_2011 DESC;

---

Using the WHERE. clause you can restrict the range:

only the rows satisfying the WHERE. clause condition will be placed into the result-set.

In this case, the list of Provinces belonging to Tuscany Region will be extracted.

SELECT
 cod_pro, provincia, sigla
FROM prov2011_s
WHERE
 (cod_reg = 9);

---

Same as above: this time the list of Communities belonging to the Florence Province will be extracted.

SELECT
 pro_com, comune, pop_2011
FROM com2011_s
WHERE
 (cod_pro = 48);

---

With a WHERE. clause: you can define as many conditions as are needed

Here a list of larger Communities (population > 50,000), within the Tuscany Region, will be extracted.
And the result-set will be ordered the largest population being shown first ['DESC'].

SELECT
 pro_com, comune, pop_2011
FROM com2011_s
WHERE
(
 (cod_reg = 9) AND
 (pop_2011 > 50000)
)
ORDER BY pop_2011 DESC;

---

You can obviously use text strings as comparison values:

in pure SQL any text string has to be enclosed within single quotes.

SQLite is smart enough to recognize double quoted text strings as well, but I strongly discourage you from adopting such a bad style !!!

Furthermore: string values comparisons for SQLite are always case-sensitive.

SELECT
 pro_com, comune, pop_2011
FROM com2011_s
WHERE
( -- search independent of upper/lower case
 (Upper(comune) = 'ROMA')
);

---

When a text string contains an apostrophe, you must apply masking.

An extra single quote is required to mask every apostrophe within a text string:

e.g.: REGGIO NELL'EMILIA has to be correctly masked as: 'REGGIO NELL''EMILIA'

SELECT
 pro_com, comune, pop_2011
FROM com2011_s
WHERE
( -- search independent of upper/lower case
 (Upper(comune) = 'L''AQUILA')
);

---

You can use the approximate evaluation operator LIKE to make case-insensitive.text comparisons:

SELECT
 pro_com, comune, pop_2011
FROM com2011_s
WHERE
( -- insensitive to upper/lower case
 (comune LIKE 'roma')
);

---

And you can use the operator LIKE so to apply partial text comparison, using % or '_' as a wild-card:

Beware: do not use the dos/linix wild-cards '*' and '?'

This query will extract any Local Council containing the sub-string 'maria' within its name.

• '%maria%': everything before and after will be ignored
• '%maria': everything before will be ignored and must end with 'maria'
• 'maria%': must start with 'maria', everything after will be ignored

SELECT
 pro_com, comune, pop_2011
FROM com2011_s
WHERE
( -- insensitive to upper/lower case
 (comune LIKE '%maria%')
);

26 records when using '%maria%'
32 records when using '%m_ria%' ('Lombriasco' being one of them)

'_' being used as a wild-card for a single letter

---

Sometimes it may be useful to use a list of values as a filter:

SELECT
 pro_com, comune, pop_2011
FROM com2011_s
WHERE
( -- search independent of upper/lower case
 (Upper(comune) IN ('ROMA', 'MILANO', 'NAPOLI'))
);

Note: Without the use of Upper, no results would be returned.

---

Another, not often used but sometimes useful, to set a range of values as a filter:

SELECT
 pro_com, comune, pop_2011
FROM com2011_s
WHERE
( -- Numbers from/to (inclusive)
 (pop_2011 BETWEEN 1990 AND 2010)
);

First result should be: 3135 San Pietro Mosezzo 1996.000000

---

Using SQL you can set any kind of complex WHERE. clause: there are no imposed limits (other that it might become unreadable for others, if not properly formatted/documented).

This is a really fantastic a feature, offering infinite scenarios.

Here a short explanation of what the next query will (should) do:

• include any County in Central Italy (Regions: Tuscany, Umbria, Marche and Lazio)
• excluding the Livorno and Pisa Provinces
• then filter the Provinces by population:
  • including the ones within the range 300,000 to 500,000 (inclusive)
  • but also including the ones exceeding a population of 750,000
  using a subquery based on the secondary-key of the province, since the table does not contain a population field.

SELECT
 provincia, sigla, pop_2011
FROM prov2011_s
WHERE
( -- Regions: Tuscany, Umbria, Marche and Lazio
 (cod_reg IN (9, 10, 11, 12)) AND
  -- but excluding the Livorno and Pisa Provinces
 (sigla NOT IN ('LI', 'PI')) AND
 ( -- Numbers from/to (inclusive)
  (pop_2011 BETWEEN 300000 AND 500000) OR
  -- but also when
  (pop_2011 > 750000)
 )
);

Warning: in SQL the logical operator OR has a lower priority than AND.
Changing the placement of the OR-clause within brackets, will produces very different results, does it not ?

---

Another, sometimes useful, SELECT clause is: using LIMIT you can set the maximum number of rows to be extracted into the result-set
(very often you aren't actually interested in reading a very large table at all: often a preview is enough).

SELECT
 *
FROM com2011_s
-- show only 10 rows
LIMIT 10;

And that's not all:

SQL allows you set the amount of rows to skip, togeather with a limited row-set, by using OFFSET. combined with LIMIT.

SELECT
 *
FROM com2011_s
-- show only 10 rows
LIMIT 10
-- starting after row 1000
OFFSET 1000;

We've seen since now how SQL allows to retrieve single values on a row-per-row basis.

Another approach is also possible, one allowing to compute total values for the whole table, or for a group(s) of selected rows.

This implies using some special functions, known as aggregate functions.

---

SELECT
 -- return the calculated amount of rows in 'com2011_s' and call it 'com2011_count'
 Count(pop_2011) AS com2011_count,
 -- return the lowest found value of 'pop_2011' in 'com2011_s' and call it 'pop_2011_min'
 Min(pop_2011) AS pop_2011_min,
 -- return the highst found value of 'pop_2011' in 'com2011_s' and call it 'pop_2011_max'
 Max(pop_2011) AS pop_2011_max,
 -- return the calculated average value of 'pop_2011' in 'com2011_s' and call it 'pop_2011_avg'
 Avg(pop_2011) AS pop_2011_avg,
 -- return the calculated total value of 'pop_2011' in 'com2011_s' and call it 'pop_2011_sum'
 Sum(pop_2011) AS pop_2011_sum
FROM com2011_s;

This query will return a single row, representing something like a summary for the whole table:

• the Min(). function will return the minimum value found in the given column
• the Max(). function will return the maximum value found in the given column
• the Avg(). function will return the average value for the given column
• the Sum(). function will return the total value for the given column
• the Count(). function will return the number of rows (entities) found

You can use the GROUP BY clause in order to establish a more finely grained aggregation/selection by sub-groups

• Thus performing calculations on all rows that contain that particular value
• For each unique value found

---

This query will return distinct results for each County:

• for each row containing the value of the County id
• bur returning only 1 row with the result

SELECT
 -- Calculate the Min/Max,Avg/sum and amount based on this value
 cod_pro,
 -- subquery is needed since 'com2011_s' does not have a field containing the county-name, only the id
 (SELECT provincia FROM prov2011_s WHERE (com2011_s.cod_pro=prov2011_s.cod_pro)) AS province_name,
 Count(pop_2011) AS com2011_count,
 Min(pop_2011) AS pop_2011_min,
 Max(pop_2011) AS pop_2011_max,
 Avg(pop_2011) AS pop_2011_avg,
 Sum(pop_2011) AS pop_2011_sum
FROM com2011_s
 -- Group the result by this value before any calcuation is done
GROUP BY cod_pro;

110 records, first listing the Province id and name, then the calculated results.

---

And you can obviously get results for each Region simply changing the GROUP BY criteria:

SELECT
 -- Calculate the Min/Max,Avg/sum and amount based on this value
 cod_reg,
 -- subquery is needed since 'com2011_s' does not have a field containing the region-name, only the id
 (SELECT regione FROM reg2011_s WHERE (com2011_s.cod_reg=reg2011_s.cod_reg)) AS region_name,
 Count(pop_2011) AS com2011_count,
 Min(pop_2011) AS pop_2011_min,
 Max(pop_2011) AS pop_2011_max,
 Avg(pop_2011) AS pop_2011_avg,
 Sum(pop_2011) AS pop_2011_sum
FROM com2011_s
 -- Group the result by this value before any calcuation is done
GROUP BY cod_reg;

20 records, first listing the Region id and name, then the calculated results.

---

One result is:

9 Toscana 287 328.000000 358079.000000 12795.128920 3672202.000000

• 9 = Region id
• Toscana = region-name
• 287 records for the Toscana were found (out of 8081 records inside the table 'com2011_s')
• 328 = the lowest population of a town/city in the Toscana
• 358079 = the highest population of a town/city in theToscana
• 112795.128920 = the average population of a town/city in theToscana
• 3672202 = the total population of all towns/citys in theToscana

Summa summarum: The Toscana has a population of 3672202 in 2011.

---

There is another different way to aggregate rows: i.e. using the DISTINCT. clause:

SELECT
 DISTINCT cod_reg, cod_pro
FROM com2011_s
ORDER BY cod_reg, cod_pro;

However: the result is not the same as when using the GROUP BY clause:

• DISTINCT. removes non-unique rows and therefore cannot perform any calulation on those, removed, rows.
• GROUP BY is absolutely required when you have to properly control an aggregation result (i.e. for the calculations of values per 'DISTINCT' result).
  • will sort all the records by the given value
  • will loop through all the records

    when the value changes: the caluculated result will be stored as a result record

  when the loop is completed, the stored results will be returned.