Re: Self defined sort order in SELECT

"Tibor Jager" <tibor_at_despammed.com> wrote in message news:2qdq35FttuboU1_at_uni-berlin.de...
| Hello,
|
| is there any possibility to sort the result of a select-statement by self
| defined rules, before the result is passed to the application?
| For example:
|
| SELECT client, emp_status, Name FROM table ORDER BY client ASC, emp_status
| ASC
|
| returns:
|
| General Motors | Employee | Mr. Smith
| General Motors | Member of Board | Mr. Doe
| General Motors | Secratary | Mrs. Easy
| Kellogs | Employee | Mr. Bush
| Kellogs | Member of Board | Mr. Kerry
| Porsche | Employee | Mr. Snyder
| Porsche | Member of Board | Mrs. Porsche
| Porsche | Secratary | Mrs. Nice
|
| but I would like to have the result displayed like this:
|
| General Motors | Member of Board | Mr. Doe
| General Motors | Employee | Mr. Smith
| General Motors | Secratary | Mrs. Easy
| Kellogs | Member of Board | Mr. Kerry
| Kellogs | Employee | Mr. Bush
| Porsche | Member of Board | Mrs. Porsche
| Porsche | Employee | Mr. Snyder
| Porsche | Secratary | Mrs. Nice
|
| Thank you for every good advise!
|
| Tibor
|
|

it would be helpful if you could state what your sort criteria is, rather than have us extrapolate it from you sample data. also version numbers are helpful -- especially in this case where you've probably got to use functions and operators that differ from 8i to 9i to 10g

anyway, you need to use expressions for your sort criteria (decode and case will help for transformations), or possible a stored function (to encapsulate complex expressions and enhance reusability). in your case it looks like you've also got denormalized data, so you may need to use standard oracle functions like INSTR and SUBSTR to parse out the portion of the column (i.e., name) that you want to sort on

keep in mind that you can sort of expressions and columns that do not appear in the select list

++ mcs Received on Fri Sep 10 2004 - 09:48:36 CDT