Detail
Lets say I have a table with a column with a few distinct values. I want to select, and get the rows back in an order that reflects the object type defined in that column.
For instance, I have a table with 3 columns:
CREATE TABLE people
(first_name VARCHAR2(20),
last_name VARCHAR2(20),
occupation VARCHAR2(20));
Then, I add some rows:
insert into PEOPLE (FIRST_NAME,LAST_NAME,OCCUPATION)
values ('joe','the','plumber');
insert into PEOPLE (FIRST_NAME,LAST_NAME,OCCUPATION)
values ('bob','the','builder');
insert into PEOPLE (FIRST_NAME,LAST_NAME,OCCUPATION)
values ('babe','ruth','baseball player');
insert into PEOPLE (FIRST_NAME,LAST_NAME,OCCUPATION)
values ('john','mccain','senator');
insert into INVLOAD.PEOPLE (FIRST_NAME,LAST_NAME,OCCUPATION)
values ('bill','gates','developer');
Now, I want to order by their relative contributions to society. Now, I could add an additional column - that indicated this value (meaning to society). Or, I could create an occupation table that contains the value to society, and then join with that table, and order by it.
However, if there are relatively few possible values, and I want the order defined in the query rather than in tabular data, then I can simply do a custom order in SQL with the decode keyword. Decode maps keys to new values. Details of how it works should be obvious looking at this sql:
select * from people
order by decode(occupation, 'senator', 'a', 'developer', 'b', 'baseball player', 'c', 'shephard', 'd', 'z')
This retrieves the rows in alphabetical order according to my decoding indicating the ordering.
|
