[SOLVED] ORDER BY `col1` IF `col2` = "value"

[kristofferlc]

I have a little problem with making the table i'm working with work as indented.

I have 15 colons split like this

col1, col1A, col1B,

col2, col2A, col2B,

and so on, till col5B.

 

Now, if i have three rows looking like this:

row one: col1 = good_val,  col1A = xx,  col2 = bad_val,  col2A = xx...

row two: col1 = bad_val,  col1A = xx,  col2 = good_val,  col2A = xx...

row one: col1 = good_val,  col1A = xx,  col2 = bad_val,  col2A = xx ...

 

Now i need the output of my query to do something like

[ORDER BY `col1A` IF `col1` = "good_val" OR `col2A` IF `col2` = "good_val"]

basically i need the query to order by different colons if some other colons have the right value...

 

That's where i get stuck, i don't really know how to do this the smartest way. Anyone have any good suggestions on how to do it properly?

 

Any help or suggestions would be great, thanks.

[Mchl]

Erm... you can't have a result sorted on two columns at once...

Could you provide an example on how would you like to get example data sorted?

[kristofferlc]

In the example data i made in my first post i would like it to sort after col1A in the first and third row, since col1 = good_val, and in row 2 i'd like it to sort after col2A since the col2 = good_val...

 

I was thinking maybe i should join the cols in some way? i just don't know how... :S

Hope you can follow me, if not i'l try explaining in an other way...

[Mchl]

Hmm... maybe like this?

 

SELECT 
*,
IF(col1 = 'good_val',col1A,IF(col2 = 'good_val',col2A)) AS sortcolumn
FROM
table
ORDER BY
sortcolumn

[kristofferlc]

Okay, it looks like something i could use, but i tried using it in my test table like this:

SELECT * , IF( ean1 =  '9123864558', ean1P, IF( ean2 =  '9123864558', ean2P ) ) AS sortcolumn
FROM 
TABLE 
ORDER BY sortcolumn

 

but got an error back looking like this:

 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')) AS sortcolumn

FROM

table

ORDER BY

sortcolumn' at line 3

 

It looks like the solution i'd want, but cant say for sure before i've seen it work. :-/

And thanks for helping by the way.

[Mchl]

SELECT * , IF( ean1 =  '9123864558', ean1P, IF( ean2 =  '9123864558', ean2P ,null) ) AS sortcolumn

[kristofferlc]

Yes! Made it work! Thanks allot.

Final query looks like this:

SELECT * , IF( ean1 =  '9123864558', eanP1, IF( ean2 =  '9123864558', eanP2 ,null) ) AS sortcolumn
FROM
vare_a
WHERE (`ean1` = 9123864558 OR `ean2` = 9123864558)
ORDER BY
sortcolumn

 

Thanks Mchl.