about sequence of rows

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
bugthefixer
Forum Contributor
Posts: 118
Joined: Mon Mar 22, 2004 2:35 am

about sequence of rows

Post 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]
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post by magicrobotmonkey »

add a column of type auto_increment. Then you can sort by that column to get them back in the same order.
fractalvibes
Forum Contributor
Posts: 335
Joined: Thu Sep 26, 2002 6:14 pm
Location: Waco, Texas

Post 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
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Post 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.
fractalvibes
Forum Contributor
Posts: 335
Joined: Thu Sep 26, 2002 6:14 pm
Location: Waco, Texas

Post by fractalvibes »

Bill,
are you replying to the other thread where you are looking for nonsequential values?


fv
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Post by Bill H »

Yep, somehow this got in the wrong thread.
Post Reply