mySQL ORDER BY temporary calculated column

Question

First you must know: I have two tables "USERS" and "SONGS"

How is possible to check some conditions and place the result on a temp column? For example foreach song in result of select must be calculate the bool and add it to temp column so I can do other calculations then and make it the primary order......

$USER = SELECT * FROM USERS WHERE SOMECONDITION

SELECT * FROM SONGS
------
(
IF SONGS.LANG  LIKE $USER[LANG]  THEN TEMPCOLUMN.ADD "1" ELSE TEMPCOLUMN.ADD 0
IF SONGS.GENR1 LIKE $USER[GENR1] THEN TEMPCOLUMN.ADD "1" ELSE TEMPCOLUMN.ADD 0
IF SONGS.GENR2 LIKE $USER[GENR2] THEN TEMPCOLUMN.ADD "1" ELSE TEMPCOLUMN.ADD 0
)
------
ORDER BY TEMPCOLUMN DESC

Yes, i want to do some calculations to the binary string then and make the order! :) — Stavros Koureas, Nov 11 '14 at 18:22
The tempcolumn will be a big mix af conditions as one condition adds one digit to the column! — Stavros Koureas, Nov 11 '14 at 18:26

score 1 · Accepted Answer · answered Nov 11 '14 at 18:28

1

This should do the same thing:

SELECT * FROM SONGS
ORDER BY
  SONGS.LANG LIKE $USER[LANG] DESC,
  IF SONGS.GENR1 LIKE $USER[GENR1] DESC,
  IF SONGS.GENR2 LIKE $USER[GENR2] DESC;

answered Nov 11 '14 at 18:28

Jimmy T.

4,033
2
22
38

So with this way, the songs matches the users language and the songs matches the genre1 and the songs matches the users genre2 came up first? – Stavros Koureas Nov 11 '14 at 18:30
I haven't tested it but it should do that. – Jimmy T. Nov 11 '14 at 18:33
Thanks for awnser, also i must ask, this code can place the song that matches the genre1 at the top if no one can match the language? – Stavros Koureas Nov 11 '14 at 18:37
Yes. Same for genre2 if there is none with matching lang or genre1. – Jimmy T. Nov 11 '14 at 18:40

mySQL ORDER BY temporary calculated column

1 Answers1