fetch rows order in user-defined order

fetch rows order in user-defined order

Post by Melo » Mon, 22 Dec 2003 05:35:20


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.
 
 
 

fetch rows order in user-defined order

Post by Jim Kenned » Mon, 22 Dec 2003 08:21:23

You would have to have a column that specifies the order and order on that.
Possibly call the column position.
(as he changes the display order you change the value in the position column
to match the order the customer wants)
Jim

 
 
 

fetch rows order in user-defined order

Post by Melo » Mon, 22 Dec 2003 20:01:40


This sounds good, thanks!
 
 
 

fetch rows order in user-defined order

Post by joe.celk » Wed, 24 Dec 2003 04:05:36

>> 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. <<

Not quite right. GROUP BY has no implied ordering; some of the
earlier SQL engines did grouping with a sort and that was a side
effect. Other products use hashing and you get the smaller buckets
back first.

The ORDER BY clause is part of a cursor and not a query; cursors
convert a result set into a sequential file structure, so that
ordering makes sense.

to let the shop owner decide in which order the products appear on a
web page. <<

Create a table with two columns like (upc, sort_order), join it to the
target table and do an ORDER BY on the sort_order column. The
sort_order column should be an integer. There are some other posting
that give you the code to move things around in the list, but they are
just simple algebra and a CASE expression in an UPDATE statement.