how to insert data in the first column of a table in MySQL

[vijcbe]

Hello friends!!

I have a question..

Let me have a table which consists the details of 5 employees, starting from employee id 1 to 5 as given below.

emp_id fname ... .... address
1 john ... ... ...
2
.
.
.
5 Shiva ............... .

What should I do if I want to include a data in the position of employee john so that the series of data pushed to one column downwards and so on. This should happen in every occasion when I insert a new employee detail. I am using MYSQL database.

Please someone help me doing it.
Thanks a million in advance.

VJ

[jx2]

Hello friends!!

emp_id fname ... .... address
1 john ... ... ...
2
.
.
.
5 Shiva ............... .

i'm not qute sure i understand you
you want new_Emploee_ID= 1 ? if that is not a case the order in table is not a problem you can use

select * from myTable order by DESC 
 

that woud give you last employee as first
but i am not sure if that is what you wanted

regards
jx2

[Atli]

Hi VJ.

Do you mean that you want the latest row inserted into the table to be at ID number 1, adding one to the ID of all the old rows?

If so, that is a very very bad idea. It would pretty much suck the life out of your server every time you added a row.
Imagine having a million rows. Every time you added a row, each of those million rows would have to be updated!

A far more sensible way is to do what we all do. Give the new row the lowest unoccupied ID.

[vijcbe]

heya Atli,
I dont want to change the ID number of an employee each an everytime. When we insert a new data, it gets inserted to the bottom of the table. I want it to get inserted at the top of the table. So, whatever the new data I insert, that should be the in the first position of the table. Is it possible??

Thanks,
VJ.

[Atli]

The top row of the table will always be the first row you insert and the last row the latest row you insert, unless you do something like what I suggested in my previous post.

You can, however, reverse the order in which MySQL displays the rows, effectively putting the latest row at the top. Like what jx2 suggested in his post, using the ORDER BY clause.

So, for example, if you have these rows:

ID  NAME
1   John
2   Jane
3   Shane
 

An "ORDER BY `ID` DESC" clause will display them like this:

ID  NAME
3   Shane
2   Jane
1   John
 

[pbmods]

Heya, VJ.

You can force MySQL to re-order the default ordering of the table by using ALTER TABLE:

ALTER TABLE `tableName` ORDER BY `ID` DESC;
 

I wouldn't recommend using this code in production, as it will get *slow* for large datasets, but it does do what I think is what you're asking for.

If you're looking for a faster, more efficient solution, though, go with Atli's suggestion.

Be sure to index the ID column, as then MySQL won't have to manually sort the result set.