Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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 ;)

like image 408
waldo Avatar asked Feb 26 '09 17:02

waldo


People also ask

Can we use expression in ORDER BY clause?

An ORDER BY clause can have an unlimited number of expressions, but will be valid even with a singleton.

What is the use of ORDER BY 1 clause?

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.

How do you write a Boolean expression in SQL?

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'.

Can we use Boolean in SQL?

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.


3 Answers

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
like image 112
Alex Barrett Avatar answered Oct 09 '22 13:10

Alex Barrett


You could try doing a

select (user_id <> ?), user_id

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

like image 33
Ed Greaves Avatar answered Oct 09 '22 13:10

Ed Greaves


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.

like image 26
chaos Avatar answered Oct 09 '22 12:10

chaos