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