Delete Duplicates

[lordrom1]

Hi

I need to delete duplicate rows (or create new table without them) but the duplicate is not the entire row of data.

Date Time LeagueMatchingId League HomeMatchingId HomeName AwayMatchingId AwayName HomeScore AwayScore HomeRedCardCount AwayRedCardCount FTOU FTOverOdds FTUnderOdds 20121202 10/02/2014 20:34:02 17 FRANCE LIGUE 1 147 Lorient 154 Toulouse 0 0 0 0 1.5 0.88 -0.98 20121202 10/02/2014 20:34:02 17 FRANCE LIGUE 1 147 Lorient 154 Toulouse 0 0 0 0 1.5 0.88 -0.98 20121202 10/02/2014 20:34:03 17 FRANCE LIGUE 1 147 Lorient 154 Toulouse 0 0 0 0 1.5 0.88 -0.98 20121202 10/02/2014 20:34:03 17 FRANCE LIGUE 1 147 Lorient 154 Toulouse 0 0 0 0 1.5 0.88 -0.98 20121202 10/02/2014 20:34:04 17 FRANCE LIGUE 1 147 Lorient 154 Toulouse 0 0 0 0 1.5 0.88 -0.98 20121202 10/02/2014 20:34:04 17 FRANCE LIGUE 1 147 Lorient 154 Toulouse 0 0 0 0 1.5 0.88 -0.98 20121202 10/02/2014 20:34:04 17 FRANCE LIGUE 1 147 Lorient 154 Toulouse 0 0 0 0 1.5 0.88 -0.98 20121202 10/02/2014 20:34:05 17 FRANCE LIGUE 1 147 Lorient 154 Toulouse 0 0 0 0 1.5 0.88 -0.98 20121202 10/02/2014 20:34:05 17 FRANCE LIGUE 1 147 Lorient 154 Toulouse 0 0 0 0 1.5 0.88 -0.98 20121202 10/02/2014 20:34:05 17 FRANCE LIGUE 1 147 Lorient 154 Toulouse 0 0 0 0 1.5 0.88 -0.98 20121202 10/02/2014 20:34:06 17 FRANCE LIGUE 1 147 Lorient 154 Toulouse 0 0 0 0 1.5 0.88 -0.98 20121202 10/02/2014 20:34:07 17 FRANCE LIGUE 1 147 Lorient 154 Toulouse 0 0 0 0 1.5 0.82 -0.92
In this example I am only interested in the first and last rows, since the ones in between have the same data in the last few columns. They are not true duplicates since the time stamp for each is different.

Any advice on the best way would be appreciated. Currently thinking I need to compare each and every row to the prior row in VBA and delete if criteria match.

Thanks

 

[Mihail]

Create a new table with exact same fields.
Create indexes-unique (using the Indexes window) in order to not allow duplicates.
Copy the records from the first table and paste in this one.

 

[lordrom1]

Sorry, I've not made it at all clear.

The price rows are time sequential. Prices can go up and down and I want to keep all rows where there is a new price.

So for example prices might do this over time:-
1.8*
1.8
1.78*
1.78
1.78
1.77*
1.77
1.78*
1.80*

So the rows I want to keep are '*' since these are new changes. The trouble is that the prices are repeated. So I only want to check the price against the previous row to see if it is a duplicate.

The database is about 70GB in disk size so I'm hoping there is built in function otherwise api have a lot of looping to do.

 

[Mihail]

The same answer.

 

[lordrom1]

well i'm not sure it is?

the uniqeness is relative to the prior row, not all rows