Using boolean expression in order by clause

问题:

I have an order by clause that looks like:

( user_id <> ? ), rating DESC, title

Where ? is replaced with the current user's id.

On postgresql this gives me the ordering I'm looking for i.e. by current user, then highest rating, then title (alphabetically).

However on MySQL I get an unclear order current user is neither first nor last, nor is it by rating or title.

Is my only option for cross database compatibility to replace this quick and dirty boolean expression with a CASE WHEN .. THEN .. ELSE .. END statement?

Edit: Thanks all for the assistance, it is as correctly pointed out by Chaos and Chad Birch the case that the problem lies elsewhere (specifically that I'm using the results of the above query as input into the next - then acting surprised that the order of the first is lost ;)

回答1:

I tested several variations on this in mysql and they all worked correctly (the way you're expecting). I suppose your problem has to be somewhere other than the query. To verify for yourself, I suggest running an equivalent query directly from mysql client.

回答2:

MySQL has no real notion of booleans, and simply maps TRUE and FALSE to the numeric values 1 and 0 repectively.

In this case user_id <> ? will return 0 for the majority of the rows in your table and 1 for the other rows. The default sort order is ASC, meaning in all likelihood the rows you want are at the bottom of your result set (0/FALSE come before 1/TRUE). Try modifying your query to accommodate this.

( user_id <> ? ) DESC, rating DESC, title

Assuming this is indeed the issue, cross-database compatibility can be achieved with ease.

IF(user = ?, 0, 1), rating DESC, title

回答3:

You could try doing a

select (user_id <> ?), user_id

to see that you are getting the right true/false values showing up.