List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:December 17 2006 4:48am
Subject:Re: Sort Select by List
View as plain text  
In the last episode (Dec 16), Dan Nelson said:
> In the last episode (Dec 16), Mark Leith said:
> > Pintér Tibor wrote:
> > >Keith Spiller írta:
> > >>I'm wondering how I would turn three different queries:
> > >>
> > >>    SELECT * FROM team WHERE office = 'Exec'
> > >>    SELECT * FROM team WHERE office = 'VP'
> > >>    SELECT * FROM team WHERE office = 'Dir'
> > >>
> > >>Into one query with the sort order of office = 'Exec', 'VP', 'Dir'...
> > >>Thanks,
> > >>  
> > >order by right(office,1)
> > >
> > >or make an extra column for ordering
> > 
> > Or be really smart :)
> > 
> > SELECT * FROM team ORDER BY office='Exec' DESC,office='VP' DESC,
> > office='Dir' DESC;
> 
> More efficient would be to use the FIELD function:
> 
> SELECT * FROM team ORDER BY FIELD(office,"Exec","VP","Dir");

Oops.  I only read the replies and not the original post.  Assuming
there are many other values for the "office" field, you might want

SELECT * FROM team WHERE office = 'Exec' UNION
SELECT * FROM team WHERE office = 'VP'  UNION
SELECT * FROM team WHERE office = 'Dir';

-- 
	Dan Nelson
	dnelson@stripped
Thread
Sort Select by ListKeith Spiller16 Dec
  • Re: Sort Select by ListKeith Spiller16 Dec
  • Re: Sort Select by ListPintér Tibor16 Dec
    • Re: Sort Select by ListMark Leith16 Dec
      • Re: Sort Select by ListDan Nelson17 Dec
        • Re: Sort Select by ListDan Nelson17 Dec
          • Re: Sort Select by ListMark Leith17 Dec
  • Re: Sort Select by ListKeith Spiller17 Dec