if(r =1 order by time1, else order by time2) - how could i do this.

[MadnessRed]

I have 2 times, in mysql database, time1 and time2, and I want to sort by time, if r is 1 then I want to use the second time value, if it is 0 then I want to use the first.

 

Is this possible? here is some pseudo code

 

ORDER BY (if `r`='1'? `time2` : `time2`) ASC

here is an example or the sorted data

 

r

time1

time2

0

7

14

1

2

8

1

3

9

0

10

21

0

11

22

[Mchl]

I'm afraid you can't do this in a simple way. In fact right now I don't see any way of doing it with just one query...

This is wicked!

[MadnessRed]

ok thanks, I suppose than I should create another row called timex, which is time1 by default then when I set r to 1, it takes the value of time 2

 

thanks anyway though

[Mchl]

Got an idea

 

SELECT *, IF(r=0,time1,time2) AS timex FROM table ORDER BY timex ASC

 

 

[edit]

 

r=0 not r=1

[MadnessRed]

Unknown column 'timex' in 'where clause'

SELECT *, IF(r=1,end_time,start_time) AS `timex` FROM table WHERE `timex` < '1234631234' ORDER BY `timex` ASC ;

[Mchl]

You can't use aliases in WHERE clasue

 

Use

SELECT *, IF(r=1,end_time,start_time) AS `timex` FROM table WHERE `time1` < '1234631234' AND `time2` < '1234631234' ORDER BY `timex` ASC ;

[corbin]

I might be entirely off on this, but you could possibly use HAVING.  The condition on time1 and time2 is probably faster though.

 

 

By the way, why quote numeric values?  MySQL just strips out the quotes.

[Mchl]

Yeah... I seriously hope you're not storing those times as strings.