user6308605 user6308605 - 6 months ago 10
SQL Question

organizing query result in sql developer (oracle 11g)

Currently i have a table called

schedule
in db (SQL developer).

Assuming the
availableID
s are 1,3,7,8. The table consists of something like this:

Stud Title Supervisor Examiner availableID
abc Hello 1024 1001 1
def Hi 1024 1001 1
ghi Hey 1002 1004 1
xxx hhh 1020 1011 1
jkl hhh 1027 1010 1
try ttt 1001 1011 1
654 bbb 1007 1012 1
gyg 888 1027 1051 1
yyi 333 1004 1022 3
fff 111 1027 1041 3
ggg 222 1032 1007 3
hhh 444 1007 1001 3
ppp 444 1005 1072 7
ooo 555 1067 1009 7
uuu 666 1030 1010 7
yyy 777 1004 1001 7
qqq yhh 1015 1072 8
www 767 1017 1029 8
eee 566 1030 1020 8
rrr 888 1004 1031 8
abc 5555 1045 1051 8


As you can see, I have sort these value using
ORDER BY availableID asc
.
However, I would like to ORGANIZE them again into something like this:

Stud Title Supervisor Examiner availableID
abc Hello 1024 1001 1
def Hi 1024 1001 1
ghi Hey 1002 1004 1
xxx hhh 1020 1011 1
yyi 333 1004 1022 3
fff 111 1027 1041 3
ggg 222 1032 1007 3
hhh 444 1007 1001 3
ppp 444 1005 1072 7
ooo 555 1067 1009 7
uuu 666 1030 1010 7
yyy 777 1004 1001 7
qqq yhh 1015 1072 8
www 767 1017 1029 8
eee 566 1030 1020 8
rrr 888 1004 1031 8
jkl hhh 1027 1010 1
try ttt 1001 1011 1
654 bbb 1007 1012 1
gyg 888 1027 1051 1
........
abc 5555 1045 1051 8


For every
availableID
it will called four times then proceed to next
availableID
. Next it will iterate back to the lowest ID but using different other values.
Stud
must be distinct.

Is it possible to achieve this by using sql query?

Answer

You can do this with row_number() and some arithmetic. Something like:

Select t.*
From (select t.*,
             Row_number() over (partition by availableid order by stud) as seqnum 
      From t
     ) t
Order by trunc((seqnum - 1) / 4), availableid