Sql Query

1 Dec 2005 9:04 PM

Mel Weaver

Hello,
     I have a vehicle database 100,000 records with Year, Make, Model,
Body,Vin, SRP text fields. What I would like to do, is delete duplicates
records with the lowest SRP field, example below.   Looking for any
possibilities with Access or MS SQL Server.

   91 Ford F-150 Truck V14323482343   10400
   92 Ford F-150 Truck 123123123123    10233   delete this
   92 Ford F-150 Truck 123123123123    10300
   92 Ford F-150 Truck 123123123123    10198 delete this
   93 Ford F-150 Truck V12312982343   10400

Thanks
Mel

1 Dec 2005 9:14 PM

FOliveira

I suggest you use a cursor in SRP descending order.
Fetch it, enter in a loop (while @@fetch_status = 0)
Create variables to keep values of last line processed and then test if
values of old line and present line are the same. If, so, delete [where
current of your-cursor]

I hth

Fabian Oliveira

Show quote

"Mel Weaver" <Mel@[remove spam]insdirect.com> escreveu na mensagem
news:%23A02aqr9FHA.3760@TK2MSFTNGP14.phx.gbl...
> Hello,
>     I have a vehicle database 100,000 records with Year, Make, Model,
> Body,Vin, SRP text fields. What I would like to do, is delete duplicates
> records with the lowest SRP field, example below.   Looking for any
> possibilities with Access or MS SQL Server.
>
>
>   91 Ford F-150 Truck V14323482343   10400
>   92 Ford F-150 Truck 123123123123    10233   delete this
>   92 Ford F-150 Truck 123123123123    10300
>   92 Ford F-150 Truck 123123123123    10198 delete this
>   93 Ford F-150 Truck V12312982343   10400
>
> Thanks
> Mel
>

1 Dec 2005 9:52 PM

Alejandro Mesa

Try,

select *
from t1 as a
where not exists(
select * from t1 as b
where
b.Year = a.Year and
b.Make = a.Make and
b.Model = a.Model and
b.Body = a.Body and
b.Vin = a.Vin and
b.SRP > a.SRP
)
go

AMB

Show quote

"Mel Weaver" wrote:

> Hello,
>      I have a vehicle database 100,000 records with Year, Make, Model,
> Body,Vin, SRP text fields. What I would like to do, is delete duplicates
> records with the lowest SRP field, example below.   Looking for any
> possibilities with Access or MS SQL Server.
>
>
>    91 Ford F-150 Truck V14323482343   10400
>    92 Ford F-150 Truck 123123123123    10233   delete this
>    92 Ford F-150 Truck 123123123123    10300
>    92 Ford F-150 Truck 123123123123    10198 delete this
>    93 Ford F-150 Truck V12312982343   10400
>
> Thanks
> Mel
>
>
>

1 Dec 2005 10:25 PM

Mel Weaver

Alejandro Thank you very much

Show quote

"Alejandro Mesa" <AlejandroM***@discussions.microsoft.com> wrote in message
news:A436847F-ACEB-4133-AA1F-0C5C26702244@microsoft.com...
> Try,
>
> select *
> from t1 as a
> where not exists(
> select * from t1 as b
> where
> b.Year = a.Year and
> b.Make = a.Make and
> b.Model = a.Model and
> b.Body = a.Body and
> b.Vin = a.Vin and
> b.SRP > a.SRP
> )
> go
>
>
> AMB
>
> "Mel Weaver" wrote:
>
>> Hello,
>>      I have a vehicle database 100,000 records with Year, Make, Model,
>> Body,Vin, SRP text fields. What I would like to do, is delete duplicates
>> records with the lowest SRP field, example below.   Looking for any
>> possibilities with Access or MS SQL Server.
>>
>>
>>    91 Ford F-150 Truck V14323482343   10400
>>    92 Ford F-150 Truck 123123123123    10233   delete this
>>    92 Ford F-150 Truck 123123123123    10300
>>    92 Ford F-150 Truck 123123123123    10198 delete this
>>    93 Ford F-150 Truck V12312982343   10400
>>
>> Thanks
>> Mel
>>
>>
>>