Re: Self defined sort order in SELECT

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Fri, 10 Sep 2004 12:36:51 -0400
Message-ID: <P56dnVUDC8y3RdzcRVn-gg@comcast.com>

"Michel Cadot" <micadot{at}altern{dot}org> wrote in message news:4141d26b$0$32728$626a14ce_at_news.free.fr...
|

...
|
| If you have only 3 emp_status with the order you show, you can do:
| order by client, decode(emp_status,'Member of
Board',1,'Employee',2,'Secratary',3)
|
| If the rule is complicated, you can create your own order function and
use:
| order by client, my_function(emp_status)
|
| --
| Regards
| Michel Cadot
|
|

another idea and some observations...

likely either the sort ordering rules for the emp_status column, or additional status codes will be defined... so your sort order really needs to be table driven, so the user can tweak it as necessary, rather than require code rewrites.

also, looking closer at your data -- you really need to normalize it... the columns in your table should look more like

SELECT
  client_id

, emp_status_code
, courtesy_title
, Name FROM

table

10 | EMP | Mr. | Smith
10 | BRD | Mr. | Doe
10 | SEC | Mrs. | Easy
23 | EMP | Mr. | Bush
23 | BRD | Mr. | Kerry
32 | EMP| Mr. | Snyder
32 | BRD | Mrs. | Porsche
32 | SEC | Mrs. | Nice

with both CLIENTS and EMP_STATUS_CODES tables to contain attributes of those business entities, and a COURTESY_TITLES table for validation (consistency) of the courtesy titles -- your query would need to join to the CLIENTS and EMP_STATUS_CODES tables for related information, but not to the COURTESY_TITLES validation table

the EMP_STATUS_CODES table would have a SORT_ORDER column for each code, which you could reference in your ORDER BY clause (

++ mcs Received on Fri Sep 10 2004 - 11:36:51 CDT