OraFAQ Forum: SQL & PL/SQL » Sorting of columns in alternative order.

Home » SQL & PL/SQL » SQL & PL/SQL » Sorting of columns in alternative order. (11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Sorting of columns in alternative order. [message #681447]

Sat, 18 July 2020 12:58

pavansivaece@gmail.com
Messages: 1
Registered: July 2020

Junior Member

Hello All.

I have a table with Gender as a column having values (M & F) inserted randomly in it.
I want to sort the records in alternative way where M comes first followed by F and followed by M
Output should be
M
F
M
F
M
F
Could anyone pls suggest me a sql query which return the records in above sorted order.

create table t3 (GENDER char(1));
insert into t3 (GENDER)
values ('M');

insert into t3 (GENDER)
values ('F');

insert into t3 (GENDER)
values ('M');

insert into t3 (GENDER)
values ('M');

insert into t3 (GENDER)
values ('F');

insert into t3 (GENDER)
values ('F');

Report message to a moderator

Re: Sorting of columns in alternative order. [message #681448 is a reply to message #681447]

Sat, 18 July 2020 14:08

Michel Cadot
Messages: 68413
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

There are many ways, here's one:

SQL> with data as (select gender, row_number() over (partition by gender order by null) rn from t3)
  2  select gender from data order by rn, gender desc
  3  /
G
-
M
F
M
F
M
F

6 rows selected.

Report message to a moderator

Re: Sorting of columns in alternative order. [message #681449 is a reply to message #681447]

Sat, 18 July 2020 14:39

EdStevens
Messages: 1375
Registered: September 2013

Senior Member

Does it matter which 'F'row follows which 'M' row? Since the GENDER table, at least as you have presented, contains only the single column, with only 1 of 2 possible values, but many rows, what is it's purpose at all?

Report message to a moderator

Re: Sorting of columns in alternative order. [message #681450 is a reply to message #681448]

Sat, 18 July 2020 14:45

Solomon Yakobson
Messages: 3211
Registered: January 2010
Location: Connecticut, USA

Senior Member

There is no need for in-line view. Ananytic functions can be used in ORDER BY clause:

select  gender
  from  t3
  order by row_number() over (partition by gender order by null),
           gender desc
/

G
-
M
F
M
F
M
F

6 rows selected.

SQL>

SY.

Report message to a moderator

Re: Sorting of columns in alternative order. [message #681451 is a reply to message #681450]

Sat, 18 July 2020 15:18

Michel Cadot
Messages: 68413
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Forgot this, thanks to remind me.

Report message to a moderator

Previous Topic:	creating a materialized view throws "ORA-01031: insufficient privileges"
Next Topic:	ear multivalue json as rows

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Mar 20 03:37:36 CDT 2023