MySQL query ordering off by 1?

Question

The query:

select title from gamelist where match (title) against ('super mario luigi');

Returns:

+-----------------------------------+
| title                             |
+-----------------------------------+
| Super Mario and Luigi             |
| Super Mario Luigi: Inside Story   |
| New Super Mario Bros.             |
| Mario and Luigi: Partners In Time |
| Luigi: Mansion                    |
+-----------------------------------+
5 rows in set (0.00 sec)

Super Mario Luigi: Inside Story is supposed to come before Super Mario and Luigi. What am I doing wrong?

Thanks!

score 2 · Accepted Answer · answered Apr 13 '11 at 19:44

You are not specifying how the results are supposed to be ordered. The returned result order is undefined in that case. Add an explicit ORDER BY, in your case (if I understand it right)

select title from gamelist where match (title) against ('super mario luigi') 
 order by title DESC;

score 0 · Answer 2 · answered Apr 13 '11 at 19:51

By definition SQL does not guarantee ordering. As @Pekka says, if you want your results ordered in a specific way, you have to specifiy that.

In general, SQL-compliant databases will return data in the same order from one query execution to another, but that is because the engine operates the same way from run to run and is explicitly not guaranteed by the SQL specification. And if your table has new indices applied or it gets dumped & reloaded, the data may very well come back in a different order than the last time if your query doesn't use an ORDER BY clause.

MySQL query ordering off by 1?

2 Answers2