Home » SQL & PL/SQL » SQL & PL/SQL » Sorting of columns in alternative order. (11g)
Sorting of columns in alternative order. [message #681447] Sat, 18 July 2020 12:58 Go to next message
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');
Re: Sorting of columns in alternative order. [message #681448 is a reply to message #681447] Sat, 18 July 2020 14:08 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
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.
Re: Sorting of columns in alternative order. [message #681449 is a reply to message #681447] Sat, 18 July 2020 14:39 Go to previous messageGo to next message
EdStevens
Messages: 1248
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?
Re: Sorting of columns in alternative order. [message #681450 is a reply to message #681448] Sat, 18 July 2020 14:45 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2985
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.
Re: Sorting of columns in alternative order. [message #681451 is a reply to message #681450] Sat, 18 July 2020 15:18 Go to previous message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Forgot this, thanks to remind me.

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


Current Time: Mon Sep 21 08:51:56 CDT 2020