Home > Net >  How to purposely duplicate specific rows in SELECT statement?
How to purposely duplicate specific rows in SELECT statement?

Time:10-06

I want to duplicate a specific teacher so that they appear twice in the output of a SELECT statement.

Teacher Grade District
Mr. Smith 3rd West
Mrs. John 4th South
Mr. Cameron 2nd North
Mr. Cameron 2nd North
Kirk Horn 1st West

Desired result, after duplicating 'Mr. Cameron':

Teacher Grade District
Mr. Smith 3rd West
Mrs. John 4th South
Mr. Cameron 2nd North
Mr. Cameron 2nd North
Mr. Cameron 2nd North
Mr. Cameron 2nd North
Kirk Horn 1st West

What would a SELECT statement look like - without creating a new a table?

I want to do something like this but without the INSERT:
https://dba.stackexchange.com/questions/142414/easiest-way-to-duplicate-rows

CodePudding user response:

If the table is big, and the filter is not selective and backed by an index, then this "trick" avoids a second sequential scan over the table - using PostgreSQL:

SELECT t.*  -- or your list of columns
FROM   test t
     , generate_series(1, CASE WHEN t.teacher = 'Mr. Cameron' THEN 2 ELSE 1 END);

fiddle

It's short syntax for a LATERAL join. See:

If you need more copies, just replace '2' above.

Postgres has a hard time estimating the number of rows to expect with this construct, which may confuse query planning. Stu's variant (doing the same) is slightly more expensive, but easier to estimate for query planning. Syntax needs to be adapted for Postgres:

SELECT teacher, grade, district
FROM   test t
JOIN   LATERAL (VALUES (1),(2)) x(v) ON v = 1 OR teacher = 'Mr. Cameron';

fiddle

CodePudding user response:

You could use UNION ALL

select Teacher,Grade,District
from test
union all
select Teacher,Grade,District
from test 
where Teacher='Mr. Cameron'
order by Teacher;

https://dbfiddle.uk/rBpSL8NW

If you want to force/predict how many duplicate values for Mr. Cameron you will add ,try below query which add only one duplicate value limit 1

(select Teacher,Grade,District
 from test
 )
union all
(
select Teacher,Grade,District
from test 
where Teacher='Mr. Cameron'
limit 1 
  );

https://dbfiddle.uk/uUX5QD3F

CodePudding user response:

You can make use of a rows constructor here with a lateral join.

You've tagged two different RDBMS engines, both support the following, a MySql solution looks like

select Teacher, Grade, District
from t
join lateral (values row(1),row(2))x(v)
where v = 1 or Teacher = 'Mr. Cameron';
  • Related