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 ;)
An ORDER BY clause can have an unlimited number of expressions, but will be valid even with a singleton.
it means order by the very first column from the select list. Cheers, Manik. sort the first column in the ascending order from the selection list.
You can insert a boolean value using the INSERT statement: INSERT INTO testbool (sometext, is_checked) VALUES ('a', TRUE); INSERT INTO testbool (sometext, is_checked) VALUES ('b', FALSE); When you select a boolean value, it is displayed as either 't' or 'f'.
In SQL Server, a Boolean Datatype can be created by means of keeping BIT datatype. Though it is a numeric datatype, it can accept either 0 or 1 or NULL values only. Hence easily we can assign FALSE values to 0 and TRUE values to 1.
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
You could try doing a
select (user_id <> ?), user_id
to see that you are getting the right true/false values showing up.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With