mysql ordering

corblimey

Does anybody happen to know what sort of order mysql uses when the ORDER BY field is the same? I have a table with 6 records in it:

a    1
b    1
c    1
d    1
e    1
f    1

if I order by the first field, it's fine, but if I order by the second field, it seems random to me. I thought it would default to the order in which the records were added to the table, but that doesn't appear to be the case. Any clues?

amen

it the data you are ordering on is the same you are going to get random results (same for any db)
the order it comes back in may depend on the statistics on the table, the last time a row was updated, the physical storage of the data etc
basically you shouldn't be ordering on a column if all the data is the same in the column

seamus

When duplicates/conflicts occur in the ORDER BY, MySQL does an ORDER BY ASC on the Primary Key.

I'm not sure what it does if there's no primary key.

corblimey

I have a composite primary key, but it doesn't appear to be using that either.

the order it comes back in may depend on the statistics on the table, the last time a row was updated, the physical storage of the data etc
basically you shouldn't be ordering on a column if all the data is the same in the column

I always assumed it was the order in which the data was added to the table for SQL Server et al. Strange.

I'll try adding a unique primary key and using that.