= = = 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@xxxxxxxxxxxxxxxxxxxxxx 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
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
