Home » SQL & PL/SQL » SQL & PL/SQL » require output in described order (oracle 11g)
require output in described order [message #638786] Sun, 21 June 2015 03:20 Go to next message
jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum
Senior Member

>create table test(gender varchar2(1))


>insert into test values('M');

>insert into test values('M');

>insert into test values('M');

>insert into test values('M');

>insert into test values('F');

>insert into test values('F');

>insert into test values('F');

>

select * from test


output:-
gender
M
M
M
M
F
F
F

required output is:-

gender
M
F
M
F
M
F
M



[Updated on: Sun, 21 June 2015 03:25]

Report message to a moderator

Re: require output in described order [message #638787 is a reply to message #638786] Sun, 21 June 2015 04:25 Go to previous messageGo to next message
Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here's one option:
SQL> select * from test;

G
-
M
M
M
M
F
F
F

7 rows selected.

SQL> with trn as
  2    (select gender,
  3            row_number() over (partition by gender order by rownum) rn
  4     from test
  5    )
  6  select gender
  7  from trn
  8  order by rn, gender desc;

G
-
M
F
M
F
M
F
M

7 rows selected.

SQL>
Re: require output in described order [message #638788 is a reply to message #638787] Sun, 21 June 2015 05:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

When ORDER BY is useless/meaningless in an analytical function I prefer to use ORDER BY NULL which prevents from questions like "why ORDER BY ROWNUM?" or "what is the meaning of ORDER BY ROWNUM?" (which will be hard to explain how it works).

Edit: English

[Updated on: Sun, 21 June 2015 14:30]

Report message to a moderator

Re: require output in described order [message #638789 is a reply to message #638788] Sun, 21 June 2015 08:37 Go to previous message
jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum
Senior Member

thanks @lf & @michel

[Updated on: Sun, 21 June 2015 08:37]

Report message to a moderator

Previous Topic: Managing the backup tables
Next Topic: date format from string
Goto Forum:
  


Current Time: Wed Jul 29 02:16:21 CDT 2026