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

Peter peter at pajamian.dhs.org
Thu Jan 28 03:38:14 UTC 2010


On 28/01/10 06:12, Paul Jordan wrote:
> The OP is wanting the results to be weighted by the position of the word
> red in his comma separated list.

This can be done from an ORDER BY although the sql would be more complex
and specific to the db you're using (mysql would be different from
postgresql).  I do believe it is best to try to push this operation off
to the db and keep it in one query instead of using multiple queries to
get this sort.

> 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).

Yes, I would be happy to discuss this with you in private.

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

Yeah, something like that, I don't know the details of how I would
approach it myself without looking into it a bit more.

> 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 easy.  Just do:

ORDER BY color LIKE 'red%' DESC

> 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)

You can do that, but only if you're sure that red will always be
followed by that character.  If not then there are other ways to deal
with this which are more flexible but again require db specific sql to
implement.


Peter




More information about the interchange-users mailing list