List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:March 5 2002 5:12pm
Subject:Re: Can you skip ORDER BY & get rows back in inserted order ?
View as plain text  
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.
Thread
Can you skip ORDER BY & get rows back in inserted order ?Sam Lam5 Mar
  • Re: Can you skip ORDER BY & get rows back in inserted order ?Paul DuBois5 Mar
    • Re: Can you skip ORDER BY & get rows back in inserted order ?Sam Iam5 Mar
      • Re: Can you skip ORDER BY & get rows back in inserted order ?Paul DuBois5 Mar
      • RE: Can you skip ORDER BY & get rows back in inserted order ?Rob Emerick5 Mar