Re: retrieve rows in a specified order
Date: Sun, 21 Dec 2003 19:11:19 +0100
Message-ID: <3fe5e243$1_at_news.home.net.pl>
Paul wrote:
> 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
So as I can see it all comes down to creating an additional column which will define the order.
> Now, if the shop owners want to vary that ordering on the fly, you are
> going to have either
>
> a) write an application that allows the shop owner to do this in a
> relatively simple way (What development tool are you using?)
PHP. Yes, there will be an admin page for the shop owner.
I'm new to the databases so I'm wondering why there is no concept of "row order" in many popular databases? There must be a reason for this. I thought I would find some simple commands in MySQL which would let me move rows up or down in a table, eg. "MOVE ROW...". Why don't they exist? Received on Sun Dec 21 2003 - 19:11:19 CET