Page 1 of 1

about sequence of rows

Posted: Fri Jul 09, 2004 12:40 am
by bugthefixer
I have table which has two columns

city_id distance


now i want data to be stored in sequence as i entered it and retrieve it in the same sequence. neither city_id is order nor distance. I have inserted data many times in sequence but when i view the contents of table es the order of rows is changed. anybody can tell me why.
Secondly as i told before both of my columns are unordered so how can i implement indexes on it.previusly i had a third column in it with the name seq_id which was in ordered form but now i m not allowed to use that so i need some other way to do that may be through indexing. how can i do that.[/mysql_man]

Posted: Fri Jul 09, 2004 6:15 am
by magicrobotmonkey
add a column of type auto_increment. Then you can sort by that column to get them back in the same order.

Posted: Sun Jul 11, 2004 12:08 am
by fractalvibes
Even with an index and/or autoincrement column there is no guarantee of the order by which the values are retrieved. You must ORDER BY in you SQL to be 100% sure of that. So either add an autoincrement column and order by that or add a timestamp column and order by that to get the results back in order in which the rows were inserted. Reverse the order by using the DESC attribute to get the newest entries first - i.e.
Select ...
where....
Order by ID DESC

fv

Posted: Sun Jul 11, 2004 8:18 am
by Bill H
No, the numbers are completely arbitrary. They could even be the same.
The first will range form 1 to 299,999,999 and the sceond from 1 to 16.

Posted: Sun Jul 11, 2004 9:20 am
by fractalvibes
Bill,
are you replying to the other thread where you are looking for nonsequential values?


fv

Posted: Sun Jul 11, 2004 11:14 am
by Bill H
Yep, somehow this got in the wrong thread.