order results by set list

[PomonaGrange]

Hi all, I want to sort some sql results by a certain list. I don't want it sorted alphabetically or anything. I want to specify a certain list. eg "order by `office` = 'president', 'vise President', secretery'" ect. Is there anyway to do this? I didn't type this example exactly how I want it nor have I tried it. I think it gets my point across though. I appreciate any help.

[mcd]

This would be a subquery in your larger query:

(SELECT 
  CASE WHEN `office` = 'president' THEN 1
  CASE WHEN `office` = 'vice-president' THEN 2
  CASE WHEN `office` = 'secretary' THEN 3
  # fill in the rest
  ELSE 4 END) as sort

where sort is the alias we've created to prioritize certain values, so the larger query becomes something like...

SELECT col1, col2,
(SELECT 
  CASE WHEN `office` = 'president' THEN 1
  CASE WHEN `office` = 'vice-president' THEN 2
  CASE WHEN `office` = 'secretary' THEN 3
  # fill in the rest
  ELSE 4 END) as sort
FROM table
WHERE col4 = 'value'
ORDER BY sort ASC

Make sure you're table is well indexed, because this will slow a query way the hell down. In the future, when dealing with titles that have a rank, you might want to use a separate table that links the text title to an integer value.

[PomonaGrange]

Hey, Thanks. That work perfectly. I knew I had seen it done before, but couldn't remember where or how. Thanks Again.

[theb3s7]

An easier solution (if, for example you have the PKs) would as shown below.

[U]someTable[/U]
# id
  someColumn1
  someColumn2

SELECT someColumn1, someColumn2
FROM someTable 
WHERE id IN (99, 456, 47, 987, 12) 
ORDER BY FIND_IN_SET(id, '99, 456, 47, 987, 12');