Order By one of two possible fields.

[dlite922]

Hey guys,

I have a list of channels, some represent the same channel but in HD, but these HD's are further down the list. I want to sort them by the actual channel number.

For example if HBO is 300 for Standard Definition and it's high definition is at 9000, I want to see them one after the other instead of landing on different pages.

Sample Data for this example:

id 100
number 300
name HBO
source 0

id 569
number 9000
name HBO HD
source 300


As you can see the HD version's source channel number is the same as the first HBO, 300.

I want to sort by number if source is 0, else by source.

I tried concat() to concatenate the two fields, do a number sort (+0) then take the substring of first three characters so that 3009000 -> 300. But this doesn't work for all my channels because not all of them are three digits.

It would be nice if I could use IF(): if source = 0, number; else source


Any ideas is appreciated as always!




Dan

[TheServant]

I am not sure if the logic is there. What about either making another field to sort by, or change the number for HBO HD to 300. Then you can simply sort by 2 columns. First you sort by number, which would sort it by number and put your HD channels together with the SD ones, and then sort by source which would put your SD's above your HD's because all SD's (I think) have a source of 0? Hope that made sense.

[dlite922]

SOLVED.

I am not sure if the logic is there. What about either making another field to sort by, or change the number for HBO HD to 300. Then you can simply sort by 2 columns. First you sort by number, which would sort it by number and put your HD channels together with the SD ones, and then sort by source which would put your SD's above your HD's because all SD's (I think) have a source of 0? Hope that made sense.

This database has more problems, but alas I'm not in charge of it, I can only "peek" at the data.

For some reason I thought I couldn't do an IF because I read somewhere you can only do it in a stored procedure. but the IF() /function/ did the trick.

SELECT IF(source = 0, number, source) as sortField
...
ORDER BY sortField


Thanks anyhow!




Dan

[TheServant]

I thought I couldn't do an IF because I read somewhere you can only do it in a stored procedure.

So did I... Hmmm this could be very useful. So thank you Dan ;)