0

I have a query object that I am creating in coldfusion, which returns columns looking like this - score, score_1, score_2, item_id, title etc...

I want to write another query where it checks which one of the 3 columns that is score, score_1, score_2 value is greater and alias it as score. In this query I want to order the results by score desc. I tried couple things using case but not quite able to get to it. Any thoughts are appreciated. Thank you.

user747291
  • 821
  • 3
  • 20
  • 43
  • 3
    Your query is a secret? – trincot Aug 02 '16 at 19:57
  • @user747291 do you want to write your logic in CF QOQ or in Oracle? As others have said it is a duplicate if you want this within Oracle but if this is CF QOQ then QOQ does not support case however you may do it using where clase in limited cases like: `SELECT score_1 AS score FROM myQry WHERE score_1 >= score_2 AND score_1 >= score_3 UNION SELECT score_2 AS score FROM myQry WHERE score_2 >= score_1 AND score_2 >= score_3 UNION SELECT score_3 AS score FROM myQry WHERE score_3 >= score_1 AND score_3 >= score_2 ORDER BY score` – Viv Aug 02 '16 at 21:02

0 Answers0