What is happening here?

It works, just wondering why.

[rocknbil]

create table test (id int(11) primary key auto_increment, top_marker tinyint(1) not null default 0, title varchar(255));

insert into test (top_marker,title) values (0,'Title One');
insert into test (top_marker,title) values (0,'Title Two');
insert into test (top_marker,title) values (1,'Title Three, TOP');
insert into test (top_marker,title) values (0,'Title Four');

select * from test order by top_marker=1 desc;

+------------+----------+--------------------+ 
¦...id.......¦top_marker¦.....title..........¦ 
¦...3........¦....1.....¦Title Three, TOP....¦ 
¦...1........¦....0.....¦Title One...........¦ 
¦...2........¦....0.....¦Title Two...........¦ 
¦...4........¦....0.....¦Title Four..........¦ 

I would think this works because "top_marker=1" returns true, which is 1, which is greater than false, which is zero, thus descending starts with 1.

You get the same results with

select * from test order by top_marker=0 asc;

Inversely, false=0, so asc puts top_marker=1 first, as 0<1.

Is this even close to "what's happening" or are there other forces at work here?

[eeek]

Obviously (for this database system) true sorts higher than false. But why not just order by top_marker without the comparison?

[rocknbil]

Because "top_marker" is, in this scenario, 0 by default, and the secondary sort is by date. :-) I suppose I could do sort by top_marker desc, date desc, I was just experimenting with it, considering evaluation of the top_marker field by a specific value, instead of sorting by column value, which allows the sorting to take place by a dynamic value. For example,

select * from test order by top_marker=2 desc, top_marker=1 desc;

While this is the equivalent of "by top_marker desc", and the inverse is "by top_marker asc," if you want specific ordering that is not strictly asc/desc, this is a viable approach.

select * from test order by top_marker=5 desc,
top_marker=7 desc, top_marker=1 desc, top_marker=2 desc;

The desc sort will sort by record id accordingly for multiple records with the same top_marker value.

However, this won't work:

- Set one of the top_marker values to 5 so there's a 5, 2, 1.

select * from test order by top_marker=5 desc, top_marker>0 desc;

Since both 2 and 1 are > 0, it's evaluates as true, equal; so you have to do

select * from test order by top_marker=2 desc, top_marker desc;

Real world example? Say you have a login system with multiple access level values. Exchange "access_level" for top marker; when viewed by an admin, you want to sort by their access level - and you want the admin to be able to choose how they are sorted. This means the sort by will be in different order every time.

Administrators
Payroll
Human Resources
Department Managers

Administrators
Department Managers
Human Resources
Payroll

[eeek]

The desc sort will sort by record id accordingly for multiple records with the same top_marker value.

Not unless you add the record id to the order clause.

[rocknbil]

Without an order by, mySQL sorts by the auto increment field by default. So after ordering by anything in the order by, it will subsequently order any duplicate order by values by the auto increment field.