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*fromtableorderby 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 >3orderby idfamily desc, relation desc, name asc)
unionallselect*from (
  select idcostumer, name, idfamily, relation from costumer 
  where idfamily =2orderby idfamily desc, relation desc, name asc)
unionallselect*from (
  select idcostumer, name, idfamily, relation from costumer 
  where idfamily !=2and idfamily <4orderby idfamily desc, relation desc, name asc)

Link to Fiddle