Consider
with t as (select 'A' letter, 3 orderby, 'F' att from dual
union all
select 'B' letter, 2 orderby, 'N' att from dual
union all
select 'C' letter, 4 orderby, 'F' att from dual
union all
select 'D' letter, 1 orderby, 'N' att from dual
union all
select 'E' letter, 5 orderby, 'N' att from dual)I need to select the data in alphabetical order except where att='F' in which case that's the position the letter should appear in. Based on that the input I would expect to see is:
1 - B
2 - D
3 - A
4 - C
5 - E
Thank you.
I left the extra columns in to explain my logic. My algorithm is:
F-type| N-type seq| count F-type less than or equal to N-type seq| sum of the two|
a 3| | | 3 |
b | 1| 0| 1 |
c 4| | | 4 |
d | 2| 0| 2 |
e | 3| 1| 4 |
f | 4| 2| 6 |
with "sum of the two" being the ordering sequence.
SQL> with t as
2 (select 'A' letter, 3 orderby, 'F' att from dual
3 union all
4 select 'B' letter, 0 orderby, 'N' att from dual
5 union all
6 select 'C' letter, 4 orderby, 'F' att from dual
7 union all
8 select 'D' letter, 0 orderby, 'N' att from dual
9 union all
10 select 'E' letter, 0 orderby, 'N' att from dual
11 ),
12 ranked_rows as
13 ( select letter,
14 orderby,
15 row_number() over ( order by letter ) as ranking
16 from t
17 where att = 'N'
18 )
19 select t.*,
20 r.*,
21 case
22 when r.letter is not null then
23 r.ranking + 0.1 +
24 ( select count(*)
25 from t
26 where orderby <= r.ranking
27 and att = 'F'
28 )
29 else t.orderby
30 end xx
31 from t,
32 ranked_rows r
33 where t.letter = r.letter(+)
34 order by xx;
L ORDERBY A L ORDERBY RANKING XX
- ---------- - - ---------- ---------- ----------
B 0 N B 0 1 1.1
D 0 N D 0 2 2.1
A 3 F 3
C 4 F 4
E 0 N E 0 3 4.1
5 rows selected.
I would advise testing a few different scenarios :-)