At 8:16 -0800 3/5/02, Sam Lam wrote:
>I have a table like so :
>
>CREATE TABLE album_rank(
> album_id INT NOT NULL,
> rank INT NOT NULL,
> KEY(album_id)
>)
>
>I want to query on the album_id & get the results ordered by rank
>but I want to avoid doing an ORDER BY in the query because of the
>filesort that it usually triggers so I pre-ordered the rows in my
>table by inserting them in the order I wanted with a CREATE TABLE
>ordered SELECT * FROM unordered ORDER BY album_id, rank ASC.
>
>For some reason I get the data back in a different order. I've tried
>ORDER BY album_id,rank ASC & DESC in case it's a FILO or FIFO.
>
>Is there some way that I can avoid doing an ORDER BY and get my
>rows back ordered by album_id, rank they way I inserted them ?
No. That is the nature of relational databases.
You might want to consider adding an AUTO_INCREMENT column, then inserting
in the order you want them back. Then you can retrieve in that order by using
an ORDER BY on the AUTO_INCREMENT column.
