How To Set The Order Of The Rows

[jude]

Hello,

I got a problem about ordering the rows.

I couldn't keep the rows in the order of the 'id' number I entered. Whenever I refresh the table, then the rows changed their order. I found out that if I don't enter a 'id' number for each row, it will generate an 'id' number for each row itself. Then this table will order all the rows in the order of this automatically generated 'id' number. But I couldn't find a way to set up the 'id' number I want SQLyog to generate.

I hope you guys can explain a little bit for me.

Thanks.

Huang

[ashwin]

Hi,

Quote:

I couldn't keep the rows in the order of the 'id' number I entered. Whenever I refresh the table, then the rows changed their order.

The row ordering is done by the server not by SQLyog. This is because the “Auto-increment” is enabled on the column('id' in your case). So MySQL assigns sequence numbers automatically.

Quote:

But I couldn't find a way to set up the 'id' number I want SQLyog to generate.

To start with an AUTO_INCREMENT value other than 1, you can set that value with CREATE TABLE or ALTER TABLE, like this:

ALTER TABLE tbl AUTO_INCREMENT = 100;

Refer: http://dev.mysql.com/doc/refman/5.1/en/example-auto-increment.html

Thanks,

Ashwin A

[jude]

Hi Ashwin,

Thank you very much for your reply. That makes me understand this problem more.

Now, when I create a new row, the automatically generated 'id' is 10000. But I want the automatically generated 'id' is 9800, rather than 10000, how can I do that? Btw, Can I disable this “Auto-increment”?

Thanks.

Huang

'ashwin' wrote:

Hi,

The row ordering is done by the server not by SQLyog. This is because the “Auto-increment” is enabled on the column('id' in your case). So MySQL assigns sequence numbers automatically.

To start with an AUTO_INCREMENT value other than 1, you can set that value with CREATE TABLE or ALTER TABLE, like this:

ALTER TABLE tbl AUTO_INCREMENT = 100;

Refer: http://dev.mysql.com/doc/refman/5.1/en/example-auto-increment.html

Thanks,

Ashwin A

[ashwin]

Hi Huang,

Quote:

Now, when I create a new row, the automatically generated 'id' is 10000. But I want the automatically generated 'id' is 9800, rather than 10000,

You can set the 'id' as 9800 by executing query:

ALTER TABLE tbl AUTO_INCREMENT = 9800;

But if there is a row already present in the table with 'id' 9800 then you cannot create with the same 'id' as it becomes 'duplicate row'.

Quote:

Can I disable this “Auto-increment”?

Right-click on the table->Alter Table(F6)->Disable “Auto Incr?” (Refer attached image)

[Author]

Posts