In the last few issues of the Wizard, we've moved
from simple sorting to sorting via artificial fields in a
table and to artificial fields we've created on the fly.
We've done this by either adding a sorting field to a
table or through the use of Union Queries. Although
I'm a fan of Union Queries, they are difficult to
create and challenging to debug.
This month we'll look at a technique that requires
neither establishing a dedicated sorting field within a
table, nor the need to resort to a union query.
Instead, we'll use what I call “directive sorting”.
Let's start again with our table of Massachusetts
cities and populations.
Our goal is to sort this list first by size and then
alphabetically, except we want Boxboro to come
first. We want to do this without creating an artificial
field to aid us with the sort.
The first step is to construct a query as follows:
When we run this query we get:
Now this is close to what we want, except the first
city is Ashland rather than Boxboro. To solve this
problem we go to the SQL view (see June 05
for
details about how to uncover and understand the
SQL statements behind a query.)
For the query shown above, the SQL statement is:
SELECT tlkpCities.Size, tlkpCities.CITY
FROM tlkpCities
ORDER BY tlkpCities.Size, tlkpCities.CITY;
Notice that the Order By statement, which controls
sorting, refers to two fields from the table tlkpCities:
Size first and then CITY. This statement sorts first
by size, 2500 being the smallest and 7500 is the
largest. In the case where multiple cities have the
same population, the SQL statement will keep all
those records together and sort by the city name.
Carlisle, Wayland and Westford all have a population
of 7500, yet they sort alphabetically by the City
name.
So to get Boxboro to come first, we change the SQL
statement as follows:
SELECT tlkpCities.Size, tlkpCities.CITY
FROM tlkpCities
ORDER BY city = "Boxboro", tlkpCities.Size,
tlkpCities.CITY;
And when we run this we get:
Just what we were looking for: Boxboro is first even
though it's not the smallest.
To pull this apart even more, let's take a look at the
design view of this query:
Notice that the first column has been changed to
city = "Boxboro"; the query is sorting on that field.
The logic involved is to evaluate the first column and
determine if the value of city is equal to "Boxboro." If
it is, it returns a true; otherwise it returns a false. It
then sorts on this column putting any true values
first (in our case we have one Boxboro) so that
becomes our first record and then sorts by size and
then finally by city.
This is very powerful stuff. If we wanted to have
Boxboro last we just change the Sort Order to
Descending. We can even do this to more than one
city. If we want Boxboro first, Westford second and
then all remaining cities sorted by size and city
name, we simply change our Order By clause as
follows:
ORDER BY city="Boxboro", city = "Westford",
tlkpCities.Size, tlkpCities.CITY
And we get
We’ve forced Boxboro to occur first, followed by
Westford, and then the rest of the cities follow,
sorted first by size and then alphabetically by city
name.
