Re: Sorting issue

Paul's probably right.. putting the sorting values in a table would be eaiser to maintain. I don't know what I was thinking with the whole "then you don't HAVE to create a table". Both ways work.. but especially if you think the positions may change, then it'll be tons easier to update if they're in a table. -TG = = = Original message = = = >= = = Original message = = = >I need to sort the results of a DB query based on the hierarchy of positions >within an organization. Since they are not necessarily alphabetical, the >best I can come up with is to assign a numerical value in a separate table >to each position, and reference that to sort it. At 2/7/2007 01:10 PM, tg-php@gryffyndevelopment.com wrote: >Well, kind of ugly but you can do something like this: > >SELECT Position, CASE Position WHEN 'CEO' THEN 1 WHEN 'COO' THEN 2 >WHEN 'CFO' THEN 3 WHEN 'HR' THEN 4 ELSE 99 END AS PositionSort >FROM SomeTable >ORDER BY PositionSort > >That way you're not creating a whole new table to store the sorting values. If I might offer alternative advice, I *would* create a separate table of positions & sort sequence values, so that all the data can be edited in one mode (e.g., phpMyAdmin). If some of your data is in MySQL and some of it's embedded in a query in a PHP script, it will be a little bit more of a hassle to maintain and a little more cryptic for the next developer who has to figure out what you've done after you abruptly run off to Tahiti. SELECT Positions.Sort, Employees.Position, Employees.LastName, etc FROM Employees, Positions WHERE Employees.Position = Positions.Position ORDER BY Positions.Sort, Employees.LastName (Assuming more than one employee per position, I figure you'd want a secondary sort criterion.) Regards, Paul __________________________ Paul Novitski Juniper Webcraft Ltd. http://juniperwebcraft.com ___________________________________________________________ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com.