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

Angus Rogerson arogerso at admmail.uwaterloo.ca
Wed Jan 27 04:38:53 UTC 2010

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

	ORDER BY color like '%red%'

so it could come from anywhere in the comma separated list of colours?


Angus Rogerson
Retail Services, University of Waterloo

More information about the interchange-users mailing list