[ic] Custom sort algo, sort on filtered data, or concatenate two sorted queries?

[Paul Jordan]

>
> On 26-Jan-10, at 9:35 PM, Peter wrote:
>
>> On 27/01/10 08:41, John A. wrote:
>>> We have an odd sorting situation.
>>>
>>> We have a column in the products table that holds a comma-delimited
>>> list of color names in order of decreasing prominence in the item. I
>>> want to generate a list of items that include a target color, with
>>> those items that have the target color listed first at the top of the
>>> list.
>>>
>>> I was hoping there was a way to filter data, with regular expressions
>>> perhaps, before sorting, but I don't see that in MySQL so I'll have to
>>> do it in one of a few hard ways...
>>>
>>> Option 1: It would be the equivalent of concatenating the results of
>>> two sorted queries: one query returning items whose color lists start
>>> with the target color, and another returning items whose color lists
>>> include but do not start with the target color. I could do that
>>> easily, but I'm not sure how to effectively combine them in a "more"
>>> scheme. Since the resulting lists would sometimes be hundreds of items
>>> long, I'd want to present it in multiple pages.
>>>
>>> Option 2: Since the color names are all alphabetic, it could also be
>>> done by replacing the target color in each color list with "1" for the
>>> sake of sorting. (But not in the actual item data.) I suppose this
>>> could be accomplished by making a color list column for each color and
>>> filling them with modified data. Not certain how to do that, though.
>>> I'd want to do it on the fly as items are added and edited.
>>>
>>> Option 3: From what I've figured out from the RTFM, there is also a
>>> way to pass a list from a query to Perl. I suppose I could, once I
>>> learned enough Perl, write my own search algorithm and result
>>> generation code.
>>>
>>> Option 4: I could continue to do it the way I am on our current
>>> non-intervchange server: Generate the pages offline with a VB program
>>> and upload them by hand. I'd really prefer to have it all
>>> automagically done, but this would work until then.
>>>
>>> Anyway, I'd like to know if anyone has any hints on how to go about
>>> any of those first three options, of if there's maybe something I
>>> hadn't thought of.
>>
>> You can sort an SQL query by a boolean expression:
>>
>> SELECT ... FROM products WHERE ... ORDER BY color = 'red' DESC
>>
>
> or
> ORDER BY color like '%red%'
>
> so it could come from anywhere in the comma separated list of colours?
>
> Angus
>
> Angus Rogerson
> Retail Services, University of Waterloo


The OP is wanting the results to be weighted by the position of the word red 
in his comma separated list.

I think if you asked an IC consultant they would just do it in perl, your 
option #3. In fact, I bet a good one like Perusion, Endpoint, Peter, etc 
could whip it up in no time (so you may want to consider that).

For Mysql, maybe you can do something creative with LOCATE, REGEXP.

If you just want to do precisely what you stated, which is not precisely 
weighted, you could just create a new return result that is a 1 if the first 
part of the string is 'red', then sort descending.

This is so untested:

SELECT this, that, IF(LEFT(color, 3) eq 'red','1','0') AS foo
FROM products
WHERE  color like '%red%'
ORDER BY foo DESC, code

I'm sure there is a syntax error in there which can be easily worked out 
when actually running this. But, the idea is if you know you are looking for 
red, then look at the first three characters in 'color', if it is red, 
assign a 1 to FOO, if not, then assign 0. The sort by FOO.

You may have to add one more character, so for 'red', you might want to 
compare for 'red,' instead so you don't run the risk of the color name being 
the beginning of another color (if even possible). (I.e., blue, blue-green, 
etc)

Paul