Databases
StackOverflow2013
Postsdbo
POOracle Order By different columns same select statement
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POOracle Order By different columns same select statement
    text
    copied!<p>I have an interesting problem here. But it is just for knowledge because I already solve it in a non elegant way.</p> <p>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.</p> <p>What I need is a way to order the data by name of holder and theirs dependants. So the sample data below</p> <pre><code>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 </code></pre> <p>Is it possible to get the above results in just one query? As you can see the order is name (just for the holders)</p> <pre><code>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 </code></pre> <p>The test case data for this example.</p> <pre><code>create table costumer ( idcostumer integer primary key, name varchar2(20), idfamily integer, relation varchar2(1) ); </code></pre> <p>This is the inserts statments for this table:</p> <pre><code>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'); </code></pre> <p>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.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload