SELECT RETURNING MULTIPLE ROWS

[puma51]

Hi

I am trying to do an insert into a table but it is returning multiple rows.

The source table contains the following:-

Plan_No, Plan_Date, Company_No, User_No
123456, 01/01/2007, 456, 5
543454, 07/02/2007, 456, 4
345654, 21/03/2007, 456, 5

My select is as follows:

insert into MIN_PLANDATE
select COMPANY_NO,min(PLAN_DATE), min(PLAN_NO), USER_NO
FROM PLANS
WHERE (company_no NOT IN (0,96862,98888))
and PLAN_DATE BETWEEN '01-Apr-2006' and sysdate
and not company_no is null
group by COMPANY_NO, PLAN_DATE, PLAN_NO, USER_NO;

I only want it to insert the row with the minimum plan_date once but it is inserting it 3 times because there are 3 plans.

Can anyone help me with this please.

Many thanks

puma

 

[lewisp]

Use the standard sub-query method to restrict your choice to the MIN date.