I am trying to come up with an easier solution and avoid the UNION ALL and a count(*) in my Query. I would always like to have 15 rows in my result set with records from the table and append blank records on top of my existing result set. The ordering should be in descending order. When I generate the additional rows, the Course Name will be blank and the position column will always be 15. Any help in this regard is greatly appreciated.
Thanks
Scripts for tables:
CREATE TABLE DUMMY_TB (ID NUMBER(2,0));
insert into DUMMY_TB (ID) values (1);
insert into DUMMY_TB (ID) values (2);
insert into DUMMY_TB (ID) values (3);
insert into DUMMY_TB (ID) values (4);
insert into DUMMY_TB (ID) values (5);
insert into DUMMY_TB (ID) values (6);
insert into DUMMY_TB (ID) values (7);
insert into DUMMY_TB (ID) values (8);
insert into DUMMY_TB (ID) values (9);
insert into DUMMY_TB (ID) values (10);
insert into DUMMY_TB (ID) values (11);
insert into DUMMY_TB (ID) values (12);
insert into DUMMY_TB (ID) values (13);
insert into DUMMY_TB (ID) values (14);
insert into DUMMY_TB (ID) values (15);
CREATE TABLE COURSES_TB ( COURSE_NAME VARCHAR2(20),
POS NUMBER(2,0));
insert into COURSES_TB (COURSE_NAME,POS) values ('Chemistry',4);
insert into COURSES_TB (COURSE_NAME,POS) values ('Physics',3);
insert into COURSES_TB (COURSE_NAME,POS) values ('Mathematics',2);
insert into COURSES_TB (COURSE_NAME,POS) values ('English',1);
insert into COURSES_TB (COURSE_NAME,POS) values ('Biology',5);
Query and Output:
select course_name, pos
from
(
select course_name, pos from courses_tb
UNION ALL
select null course_name, 15 pos
from dummy_tb
where rownum <= 15 - (select count(*) from courses_tb)
)
where pos < 16
order by pos desc;