CODEWITHSUNDEEP
mysql
NullVoxPopuli
NullVoxPopuli

Reputation: 65173

MySQL: how do I do in inline function with ORDER BY?

So, I want to create a formula for ordering things by popularity, but I don't want to create a stored procedure or function. I'd like the function just to be in my query.

So, is there a way to do something like

SELECT cols
FROM tables
WHERE conditions
ORDER BY function(){ logic using cols } DESC LIMIT 10

or something like that?

Upvotes: 3

Views: 3221

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562731

I don't want to create a stored procedure or function

You want to call a function but you don't want to define a function? Okay...

You can use expressions in the ORDER BY clause, including CASE which is pretty powerful:

SELECT cols
FROM tables
WHERE conditions
ORDER BY CASE WHEN col1 BETWEEN a AND b THEN 1 
              WHEN col1 BETWEEN c AND d THEN 2
              ...
         END
     DESC LIMIT 10

But it's often easier to do the calculation in your select-list and then order by that calculated column:

SELECT cols, POW(col1, col2) AS calc1
FROM tables
WHERE conditions
ORDER BY calc1 DESC LIMIT 10

Upvotes: 2

Related Questions