Re: retrieve rows in a specified order

From: Dawn M. Wolthuis <>
Date: Sun, 21 Dec 2003 09:44:42 -0600
Message-ID: <bs4f5h$20d$>

"Melon" <> wrote in message
> I'm starting to work with mysql (but I think my post applies to other
> databases as well) and I've come across a problem - I've read in several
> sources that the order of retrieved rows by a SELECT statement is
> undefined unless you use GROUP BY or ORDER BY. Now I need to be able to
> fetch the rows in an order defined by me or the web site's owner.
> Imagine that each row represents one product in a shop. Now I need to
> let the shop owner decide in which order the products appear on a web
> page. He needs to be able to freely change this order at any time and
> move any product to any position he wants. What would be the easiest way
> to accomplish this in a database knowing I can't change the row order? I
> will be grateful for any ideas.
> Melon.

If you are compelled to use a relational database, then you would need to add an attribute that is used for ordering. Relational databases tend to have lots of attributes and tons of tables not necessary in other flavors of data storage systems.

So, if you may select any database, then you might want to select one that can store ordered collections. Your product table would then not be ordered, but a table that stores your web pages could include ordered collections of foreign keys to the product database. Databases that would permit this are just about all except relational: XML databases (Berkeley DB-XML or Xindice, for example), object databases, PICK (such as IBM U2) databases and others (adabas, progress?).

Even relational databases now permit arrays or other types of collections in attributes. However, SQL-99 is not implemented far and wide (in particular, it is not used for ODBC). The non-relational databases typically normalize-on-the-fly for SQL or ODBC so that the nested data can be seen either as stored or in 1NF.

--dawn Received on Sun Dec 21 2003 - 16:44:42 CET

Original text of this message