Remove rows with dublicate field values

[Almeding]

Hi,

I have two tables: one with all items from a ERP-system (2), and one with manual correction to the items lists product hierachy (1). The manual corrections (1) can hold items which also will be found in the item list table (2).

The two tables are joined in an UNION query. My problem is that in case the item is found in both tables I only want to keep records from table (1).

So this table:

MinOfSYS	FirstOfHierarchy 1	FirstOfHierarchy 2	FirstOfHierarchy 3	FirstOfCOMPANY	UNITS	COST	EAN
1	ABC	DDD	KKK	AAA	6	46,04553	6.310.183.055.272
1	ABC	DDD	KKK	AAA	6	49,163	6.310.183.055.289
1	ABC	DDD	KKK	AAA	6	50,7706	6.310.183.055.296
1	ABC	DDD	KKK	AAA	6	53,9078	6.310.183.055.302
2	ABC	DDD	KKK	BBB	6	46,04553	6.310.183.055.272
2	ABC	DDD	KKK	BBB	6	49,16308	6.310.183.055.289
2	ABC	DDD	KKK	BBB	6	50,77057	6.310.183.055.296
2	ABC	DDD	KKK	BBB	6	53,90778	6.310.183.055.302
2	ABC	DDD	KKK	BBB	96	1002,82563	6.310.183.256.013
2	ABC	DDD	KKK	BBB	96	968,89833	6.310.183.256.006

<colgroup><col width="67" style="width:50pt"> <col width="157" style="width:118pt" span="4"> <col width="44" style="width:33pt"> <col width="74" style="width:56pt"> <col width="134" style="width:101pt"> </colgroup><tbody>
</tbody>

Should end up like this:

MinOfSYS	FirstOfHierarchy 1	FirstOfHierarchy 2	FirstOfHierarchy 3	FirstOfCOMPANY	UNITS	COST	EAN
1	ABC	DDD	KKK	AAA	6	46,04553	6.310.183.055.272
1	ABC	DDD	KKK	AAA	6	49,163	6.310.183.055.289
1	ABC	DDD	KKK	AAA	6	50,7706	6.310.183.055.296
1	ABC	DDD	KKK	AAA	6	53,9078	6.310.183.055.302
2	ABC	DDD	KKK	BBB	96	1002,82563	6.310.183.256.013
2	ABC	DDD	KKK	BBB	96	968,89833	6.310.183.256.006

<colgroup><col width="67" style="width:50pt"> <col width="157" style="width:118pt" span="4"> <col width="44" style="width:33pt"> <col width="74" style="width:56pt"> <col width="134" style="width:101pt"> </colgroup><tbody>
</tbody>

I have tried MIN, FIRST etc, but then I get the wrong hierachy

Any help will be much appreciated - thanks

brgds
a novice

 

[Almeding]

Forgot - the EAN-code must be unique, so it should return this (table (1)):

1

ABC

DDD

KKK

AAA

6

46,04553

6.310.183.055.272

<tbody>
</tbody>

and remove this (from table (2))

2

ABC

DDD

KKK

BBB

6

46,04553

6.310.183.055.272

<tbody>
</tbody>

 

[stumac]

If you do a group query and use the union query as the source... maybe something like:

SELECT First(MinOfSys) AS 1stSys, First([FirstOfHierarchy 1]) AS [1stHierarchy 1], First([FirstOfHierarchy 2]) AS [1stOfHierarchy 2], First([FirstOfHierarchy 3]) AS [1stOfHierarchy 3], First(FirstOfCOMPANY) AS 1stCOMPANY, First(UNITS) AS 1stUNITS, First(COST) AS 1stCOST, EAN

FROM (Select * from [COLOR=#FF0000]table1[/COLOR] UNION ALL select * from[COLOR=#FF0000] table2[/COLOR])

GROUP BY EAN;

 

[Micron]

Wondering if a subquery would work along the lines of

DELETE Table1.[MinOfSys] FROM Table1 WHERE 
(((Table1.[MinOfSys] = (SELECT [MinOfSys] AS Tmp FROM [Table1] As Dupe 
WHERE (Dupe.[EAN] = Table1.[EAN]))));

Don't try these on anything but copies of your data!