Forum Moderators: open
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?
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
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.
