asked on

Inserting oldest record into new table.

Hi,
I need to be able tom select and insert into another table the oldest records in a table.
An example of the data is as follows:

StockCode DateInserted DateTo ProductGroup OrderLine ColourCode
1234567 1 Dec 2015 1 Jan 2016 A05 385 456
1234567 7 Jun 2016 10 Oct 2016 A06 385 456

The entry I need to select and insert is the first line above. My source table contains 5.6 million rows. This would affect 500k

Any guidance would be appreciated

Many thanks

Vitor Montalvão

Generic sample for your case to copy records from last years:

INSERT INTO TargetTable (Column1, Column2, ..., ColumnN)
SELECT Column1, Column2, ..., ColumnN
FROM SourceTable
WHERE DateInserted < '20170101'

Open in new window

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Morpheus7

ASKER

Hi,
Thanks for the reply.
Sorry that was probably a bad example that I gave. Most of the dates will be different across the target dataset. Ideally, I would need to pull out by the ProductGroup, ie select MIN( ProductGroup).
Not sure how to do the insert using this for the whole dataset.

Vitor Montalvão

You can play with the SELECT part as you need. If you don't need to filter by date and prefer to copy only based in the ProductGroup:

INSERT INTO TargetTable (ProductGroup, Column1, Column2, ..., ColumnN)
SELECT MIN(ProductGroup), Column1, Column2, ..., ColumnN
FROM SourceTable
GROUP BY Column1, Column2, ..., ColumnN

Open in new window

Scott Pletcher

Adjust the PARTITION BY and ORDER BY clauses as needed to get the row left you want out of each group of rows. The PARTITION BY clause defines the group of rows, and the ORDER BY determines which one of those rows will be the first one, the one that is kept.