How to stop database table from sorting values

Question

That is a table in mysql, the 010 is expected to be the last on the table. How do I handle that? The database automatically sort the values entered which id not needed

Answer 1

What you're asking for is basically "show the rows in the sequence entered," which (strictly-speaking) is undefined in SQL.

What I usually do with my tables is add an ID column like:

create table foo (id int not null primary key auto_increment, ...rest of columns...)

This way, the rows are sortable by sequence-of-entry. It doesn't have to be the primary key, either--any auto_increment column will work. (auto_increment is a MySqlism, btw, other RDBMSes have other syntax to do basically the same thing).

BTW, your column is, strictly speaking, sorting correctly--looks like it's a char or varchar column, so it's sorting in lexicographical order rather than numeric. If you want to port your data over, I expect you can do something like:

insert into new_table
select * from old_table order by CAST(Visitorsid AS UNSIGNED)

At least I think that works...don't have a MySQL command line handy to try it :-). Good luck!

(Updated to fix MySQL syntax and use the right column name)

Answer 2

Database tables are unsorted sets, and have no intrinsic order. If you want to get the rows in a specific order, you'll have to specify it explicitly, with an order by clause:

SELECT   *
FROM     mytable
ORDER BY CAST(visitorsid AS INTEGER)

Answer 3

You could specify the order of a character field by its length, instead of standard collation order.

SELECT * FROM MY_TABLE ORDER BY LENGTH(visitorid);

I should add that by the leading 0 I assume this is not a numeric field.