Skip to content
Advertisement

How to select 7 rows or more from select query? even if table is returning less than 7 rows

I want a script that gives me an exact 7 rows. For example, if my table returns only 2 rows then we have to add another 5 rows with all columns contains a null value. But if the table returns more than 7 rows then select all returned rows. Please Help!

Advertisement

Answer

Create a dummy table with 2 Fields

CREATE TABLE dummy (
  id INT NOT NULL,
  NullVals VARCHAR(5) DEFAULT NULL,
  PRIMARY KEY (id));

Now insert these values to this table:

INSERT INTO dummy (id, NullVals) VALUES 
  (1, NULL),
  (2, NULL),
  (3, NULL),
  (4, NULL),
  (5, NULL),
  (6, NULL),
  (7, NULL);

run your query as follows:

Select acolumn from abc_table where acolumn like 'something'

union All

select NullVals from dummy
where id <= 7-(select count(*) from abc_table where acolumn like 'something');

Also you can replace the dummy table with CTE

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement