Oracle Order By different columns same select statement

Question

I have an interesting problem here. But it is just for knowledge because I already solve it in a non elegant way.

I have a table that have costumers and they can be holders or dependants and this relation is described as a family. Each family can have only a holder and 0-n dependants. A holder is identified by an H and a Dependant by a D.

What I need is a way to order the data by name of holder and theirs dependants. So the sample data below

idcostumer      name                 idfamily        relation
    1         Natalie Portman           1               H
    2         Mark Twain                3               D
    3         Carl Sagan                2               D
    4         Bob Burnquist             2               H
    5         Sheldon Cooper            1               D
    6         Anakin Skywalker          4               H
    7         Luke Skywalker            4               D
    8         Leia Skywalker            4               D
    9         Burnquist Jr.             2               D
    10        Micheal Jackson           3               H
    11        Sharon Stone              1               H
    12        Michelle Pfeiffer         3               D

Is it possible to get the above results in just one query? As you can see the order is name (just for the holders)

idcostumer      name                 idfamily        relation
    6         Anakin Skywalker          4               H
    8         Leia Skywalker            4               D
    7         Luke Skywalker            4               D
    4         Bob Burnquist             2               H
    9         Burnquist Jr.             2               D
    3         Carl Sagan                2               D
    10        Micheal Jackson           3               H
    2         Mark Twain                3               D
    12        Michelle Pfeiffer         3               D
    11        Sharon Stone              1               H
    1         Natalie Portman           1               D
    5         Sheldon Cooper            1               D

The test case data for this example.

create table costumer (
    idcostumer integer primary key,
    name varchar2(20),
    idfamily integer,
    relation varchar2(1)
);

This is the inserts statments for this table:

insert into costumer values ( 1 , 'Natalie Portman'  , 1, 'D');
insert into costumer values ( 2 , 'Mark Twain'       , 3, 'D');
insert into costumer values ( 3 , 'Carl Sagan'       , 2, 'D');
insert into costumer values ( 4 , 'Bob Burnquist'    , 2, 'H');
insert into costumer values ( 5 , 'Sheldon Cooper'   , 1, 'D');
insert into costumer values ( 6 , 'Anakin Skywalker' , 4, 'H');
insert into costumer values ( 7 , 'Luke Skywalker'   , 4, 'D');
insert into costumer values ( 8 , 'Leia Skywalker'   , 4, 'D');
insert into costumer values ( 9 , 'Burnquist Jr.'    , 2, 'D');
insert into costumer values ( 10, 'Micheal Jackson'  , 3, 'H');
insert into costumer values ( 11, 'Sharon Stone'     , 1, 'H');
insert into costumer values ( 12, 'Michelle Pfeiffer', 3, 'D');

I've tried some things, create a father sun relationship with connect by statement and familyid concatenated with the relation. Used a row_count with a over clause ordering by relation desc and family id, but this way I lost the name order.

Answer 1

If I understand you correctly, you want to first order the families by the name of the holder, then by the names of the dependents. The following does that.

with family_order as (
  select  idfamily, rownum r from (
    select idfamily from costumer where relation = 'H' order by name
  ) 
)
select c.* from costumer c 
inner join family_order fo on c.idfamily = fo.idfamily
order by fo.r, relation desc, name

Fiddle here

Answer 2

Try:

select * from table order by idfamily desc, relation desc, name asc    

Link to Fiddle

For un-natural order you can use "union all":

select * from (select idcostumer, name, idfamily, relation from costumer 
  where idfamily > 3
  order by idfamily desc, relation desc, name asc)
union all
select * from (
  select idcostumer, name, idfamily, relation from costumer 
  where idfamily = 2
  order by idfamily desc, relation desc, name asc)
union all
select * from (
  select idcostumer, name, idfamily, relation from costumer 
  where idfamily != 2 and idfamily < 4
  order by idfamily desc, relation desc, name asc)

Link to Fiddle