On 6/18/2005, "Patrick Bierans" <[EMAIL PROTECTED]> wrote:
>How can I sort a query by a special order? > >I need a query like this: > >select * from orderingtest order by rankingstring against sortlist(highest, >higher, high, aboveaverage, average, belowaverage, low, lower, lowest, '', >NULL) > >the structure might be something like this: > >create table orderingtest >( > id bigint unsigned not null auto_increment primary, > rankingstring varchar(255) >); > >insert into orderingtest set rankingstring='highest'; >insert into orderingtest set rankingstring='high'; >insert into orderingtest set rankingstring='aboveaverage'; >insert into orderingtest set rankingstring='average'; >insert into orderingtest set rankingstring='belowaverage'; >insert into orderingtest set rankingstring='low'; >insert into orderingtest set rankingstring='lowest'; >insert into orderingtest set rankingstring='high'; >insert into orderingtest set rankingstring='high'; >insert into orderingtest set rankingstring='low'; >insert into orderingtest set rankingstring='low'; >insert into orderingtest set rankingstring='average'; >insert into orderingtest set rankingstring='belowaverage'; >insert into orderingtest set rankingstring='higher'; >insert into orderingtest set rankingstring='aboveaverage'; >insert into orderingtest set rankingstring='lower'; > >I cannot use temporary tables or views and I cannot change the structure >(this sorting is far dynamic and customizable) > >Any Ideas? > > Patrick - I believe this is what you're looking for: select *, case rankingstring when 'highest' then 10 when 'higher' then 20 when 'high' then 30 when 'aboveaverage' then 40 when 'average' then 50 when 'belowaverage' then 60 when 'low' then 70 when 'lower' then 80 when 'lowest' then 90 when '' then 100 when null then 110 end as rankingorder from orderingtest order by rankingorder asc; One caveat - I've never used the case function in MySQL. Oracle has a similar function called decode, which I've used many times to do exactly this type of sorting. MySQL's CASE function appears to be the closest equivalen to Oracle's DECODE. hth, Mike Community email addresses: Post message: [email protected] Subscribe: [EMAIL PROTECTED] Unsubscribe: [EMAIL PROTECTED] List owner: [EMAIL PROTECTED] Shortcut URL to this page: http://groups.yahoo.com/group/php-list Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/php-list/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
