How to explicitly sort by the default sort order in MySQL?

Question

I have a MySQL table where there are two fields that make up the primary key, and a "sort" field. None of these are auto-incrementing.

When you do a query on the table without specifying an ORDER BY clause, it pulls the data out in the order that it was put in -- pretty standard. So whichever rows were inserted first, it pulls those out first.

The problem is that when I ORDER BY sort ASC, if sort is null, then it really doesn't sort at all and the results just become jumbled up. But I want to have the backup ORDER BY be that default order that it would otherwise pull out of. I can't have the backup be an auto-incrementing "ID" field, because there is no auto-incrementing ID field. The primary key is just a combination of two foreign keys.

-----EDIT-----

Table:

CREATE TABLE IF NOT EXISTS `product_attribute_select_value` (
  `product_attribute_id` int(11) NOT NULL,
  `attribute_select_value_id` int(11) NOT NULL,
  `sort` int(11) default '0',
  PRIMARY KEY  (`product_attribute_id`,`attribute_select_value_id`),
  KEY `product_attribute_select_value_FI_2` (`attribute_select_value_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `product_attribute_select_value`
  ADD CONSTRAINT `product_attribute_select_value_FK_1` FOREIGN KEY (`product_attribute_id`) REFERENCES `product_attribute` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `product_attribute_select_value_FK_2` FOREIGN KEY (`attribute_select_value_id`) REFERENCES `attribute_select_value` (`id`);

Data:

The data is inserted, and when pulled out without an ORDER BY clause, it comes out in the order it was put in. When there is an ORDER BY clause and all of the sort values are 0, it pulls it out in a seemingly random order.

Answer 1

Without an order by clause there is no guarantee as to the order the rows will come back. They generally do come back in the order they were inserted, but again, that is not something you can really rely on. Your best bet is to add an auto incrementing id that you can sort on as a backup. That will allow you to sort based on insertion order if that's what you want your backup sort to be.

Answer 2

First off, it is important to know that, in the absence of an ORDER BY clause, the SQL standard does not prescribe the SQL implementations to produce the rows in any particular order, in fact there is not even an expectation that the order produced be consistent for similar queries !

It is therefore generally dangerous to rely on the idea of a "natural order" provided by the implementation, unless explicitly documented by the particular DBMS producer (and I don't believe this is the case with MySQL), this "natural order" could change at any time (any new release, any change of content within the database, even!)

There is a trick to deal with null values, however:

ORDER BY COALESCE(sort, 'zzzzzzz')

where 'zzzzzz' is a value which is bigger or smaller than any expected value for the "sort" column, resuting in placing the null rows at the end or at the beginning. We typically use this technique to override the default behavior of MySQL which is to consider NULL values as the smallest, and hence placing first in an "ASC" sort and last in a "DESC" sort.
This working with null values in the MySQL documentation.

It is also possible to add an additional column for the sort, maybe something like

ORDER BY COALESCE(sort, 'zzzzzzz') , id

Answer 3

I know what you want but can't really come out with a perfect solution.

Here are my 2 alternatives:

• Sort in your app
• Add a timestamp column or autoincrement column for the sake of backup sort: .... ORDER BY sort, SORTCOLUMN ASC

Answer 4

to make sure index is used for sorting you may try also following idea:

select ... where sort is not null order by sort asc
union
select ... where sort is null

this should work in 99%, but you shouldn't rely too much that nulls will be at the end (because that is not officially guaranteed)