sql - Order rows by values -

i trying order table rank, rows have position value - have have position according value in position field. possible without additional tables, views etc?

i have table this:

rank | position | name 999  | 10       | txt1 200  | 4        | txt2 32   | 1        | txt3 1200 | 2        | txt4 123  | null     | txt5 234  | null     | txt6 567  | null     | txt7 234  | null     | txt8 432  | null     | txt9 877  | null     | txt10 

desired output have this:

rank | position | name 32   | 1        | txt3 1200 | 2        | txt4 877  | null     | txt10 200  | 4        | txt2 567  | null     | txt7 432  | null     | txt9 345  | null     | txt8 234  | null     | txt6 123  | null     | txt5     999  | 10       | txt1 

here idea. assign proper ordering each row. then, if position available use instead. when there ties, put position value first:

select t.* (select t.*, row_number() on (order rank desc) seqnum       t      ) t order (case when position not null position else seqnum end),          (case when position not null 1 else 2 end); 

sql fiddle doesn't seem working these days, query demonstrates results:

with t(rank, position, t) (       select 999, 10, 'txt1' union       select 200, 4,  'txt2' union       select 32 , 1,  'txt3' union       select 1200, 2, 'txt4' union       select 123, null, 'txt5' union       select 234, null, 'txt6' union       select 567, null, 'txt7' union       select 234, null, 'txt8' union       select 432, null, 'txt9' union       select 877, null , 'txt10'      ) select t.* (select t.*, row_number() on (order rank desc) seqnum       t      ) t order (case when position not null position else seqnum end),          (case when position not null 1 else 2 end); 

edit;

when wrote above, had nagging suspicion of problem. here solution should work. more complicated produce right numbers:

with t(rank, position, t) (       select 999, 10, 'txt1' union       select 200, 4,  'txt2' union       select 32 , 1,  'txt3' union       select 1200, 2, 'txt4' union       select 123, null, 'txt5' union       select 234, null, 'txt6' union       select 567, null, 'txt7' union       select 234, null, 'txt8' union       select 432, null, 'txt9' union       select 877, null , 'txt10'      ) select * (select t.*, g.*,              row_number() on (partition t.position order t.rank) gnum       generate_series(1, 10) g(n) left join            t            on t.position = g.n      ) tg left join      (select t.*,              row_number() on (partition t.position order t.rank) tnum       t      ) t      on tg.gnum = t.tnum , t.position null order n; 

this weird sort of interleaving problem. idea create slots (using generate series) positions. then, assign known positions slots. finally, enumerate remaining slots , assign values there.

note: hard-coded 10, easy enough put in count(*) table there.