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

[Peter]

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


Peter