Hands on: Ordering database information

The relational database is a fabulous device, but even those of us who love it would admit that it has a few weird characteristics.

However, it is worth getting to grips with these oddities, because a lack of understanding can lead you into situations where you can find no acceptable way of doing what you want to do.

What’s more, some intractable problems become easy once you understand the constraints imposed by the weirdness.

So, today’s weirdness is deceptively simple – the rows in a table are inherently unordered. To talk about the position of a row in the table is as meaningless as talking about the flavour of the number 7. We are discussing a property that the object in question simply doesn’t have.

Oddly, this doesn’t mean we can’t control the order in which rows are displayed – there are lots of ways to do this. In an SQL query, you can easily apply an order using the aptly named ORDER BY clause. For example,

SELECT Task, Date
FROM Tasks
ORDER BY Date;
will list the row in ascending order by date.

In Access, you can apply a sort order to a column in a table using the Sort Ascending/Descending buttons on the menu bar, or with the Sort row in the query grid. And even if you don’t sort a query or table, the rows usually appear sorted by primary key value.

So the fact that we often see the rows ordered gives the strong impression that they have some inherent order. They don’t. The ordering (or sorting) imposed in these cases is like a temporary filter through which we’re viewing the underlying records – which remain unordered.

This distinction sounds pedantic but in practice, it is crucial and the importance is easy to see once we start to discuss what we can and cannot do with the records.

Take the table called Tasks (pictured above). Can we sort the rows in ascending order by Duration? Yes. Can we sort them alphabetically by Task? Sure. Can we find the date of the row where the Task is “Paperwork”? No problem. Can we find the date of the row above the one where the Task is “Paperwork”? Absolutely not.

The reason is simple: the concept of ‘the row below’ or ‘the row above’ requires that the position of a row is known with respect to the other rows, and it isn’t. Each row exists in isolation, knowing nothing about the rows we might consider its neighbours.

To give another example, you cannot write a standard SQL query that says ‘take the value in this row and add it to the value in the row below’. Even the ORDER BY clause only imparts a temporary order to the rows without imparting the concept of ‘above’ or ‘below’ to the set.

Like this story? Spread the news by clicking below:

 del.icio.us     Digg this     reddit!