Oracle Order By Different Columns Same Select Statement

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 depe

Solution 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

Solution 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