Add blank records to the top of an existing result and always maintain a total of 15 rows — oracle-tech

    Forum Stats

  • 3,714,823 Users
  • 2,242,634 Discussions
  • 7,845,082 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Add blank records to the top of an existing result and always maintain a total of 15 rows

Roxy rollers
Roxy rollers Member Posts: 42 Red Ribbon

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;
Tagged:

Best Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 39,951 Red Diamond
    Accepted Answer

    By way, if you didn't already have a table like dummy_tb, you could easily generate simulate it like this:

    WITH  dummy_tb  AS
    (
      SELECT LEVEL AS d_id -- id is a SQL keyword, so it's not a good column name
      FROM  dual
      CONNECT BY LEVEL <= 15
    )
    SELECT  c.course_name
    ,	 NVL (c.pos, 15) AS pos
    FROM   dummy_tb  d
    LEFT JOIN courses_tb c  ON c.pos = d.d_id
    ORDER BY pos DESC
    ;
    


    Roxy rollers

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 39,951 Red Diamond
    Accepted Answer

    By way, if you didn't already have a table like dummy_tb, you could easily generate simulate it like this:

    WITH  dummy_tb  AS
    (
      SELECT LEVEL AS d_id -- id is a SQL keyword, so it's not a good column name
      FROM  dual
      CONNECT BY LEVEL <= 15
    )
    SELECT  c.course_name
    ,	 NVL (c.pos, 15) AS pos
    FROM   dummy_tb  d
    LEFT JOIN courses_tb c  ON c.pos = d.d_id
    ORDER BY pos DESC
    ;
    


    Roxy rollers
Sign In or Register to comment.