Re: retrieve rows in a specified order

maikel7_at_poczta.onet.pl says...

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

There is no concept of "row order" in an RDBMS (which MySQL isn't really, but anyway).

You have to determine a row order in your query - normally this is done by the primary key of the table

SELECT * FROM My_Table ORDER BY My_Key_Field

Now, you can create a field called Order_By_Field, and put, say, integers in this column in the order that you want the data to come out, say by putting your most popular item as 1, next most popular as 2, &c.

SELECT * FROM My_Table ORDER BY Order_By_Field

Now, if the shop owners want to vary that ordering on the fly, you are going to have either

1. write an application that allows the shop owner to do this in a relatively simple way (What development tool are you using?)

or

b) give him access to the database command line tool and allow him to put in his own values using SQL - I do *_NOT_* recommend this.

> I will be grateful for any ideas.

HTH. Paul...  

> Melon.

-- 

plinehan  x__AT__x  yahoo  x__DOT__x  com

C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04 W2K Pro

Please do not top-post.