Help Using Distinct 1

[mark1110]

Hi,

I have a table that has 50 fields in it. I need to get rid of duplicate records based on only 5 of those fields. I am not sure how to do that. I tried using distinct without any luck. Has anyone done this before, if so can you tell me what you did.

Thanks,

Mark

 

[r937]

the first thing you have to decide is a rule for determining which values of the other 45 fields you want to keep when you derive distinct combinations of the first 5

r937.com | rudy.ca

 

[mark1110]

What I want to do is retain all 50 columns but just weed out duplicates base on only 5 fields (i.e. name,address, city, state, zip)

Mark

 

[r937]

yes, that part was clear, but what i'm asking is which values of the other 45 do you want to keep

for example

A1 B2 C3 D4 E5 foo far fap ...
A1 B2 C3 D4 E5 boo bar bap ...
A1 B2 C3 D4 E5 koo kar kap ...
A1 B2 C3 D4 E5 soo sar sap ...

if you want only one row for the combination A1 B2 C3 D4 E5, then which values of the other 45 fields do you want? does it matter?





r937.com | rudy.ca

 

[PHV]

Hopefully you have a Primary Key column named, say, colPK :
DELETE FROM yourTable
WHERE colPK NOT IN (SELECT MAX(colPK) FROM yourTable GROUP BY name,address, city, state, zip)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mark1110]

I want one row for each of the 5 distinct fields which also includes the values for the other 45 non distinct fields. So the total number of columns in each row will be 50.

Mark

 

[r937]

one row ... which also includes the values for the other 45 fields"

i don't think you understand what i'm asking

there's more than one row for each combination

(otherwise you wouldn't even have posted the question)

which values do you want, i.e. from which row?

if it does not matter, then do this --

select column1
     , column2
     , column3
     , column4
     , column5
     , min(column6) as mincolumn6
     , min(column7) as mincolumn7
     , ...
     , min(column50) as mincolumn50
  from daTable
group
    by column1
     , column2
     , column3
     , column4
     , column5

r937.com | rudy.ca

 

[mark1110]

It worked, thx

Mark

 

[PHV]

Another way if you have a Primary Key column named, say, colPK :
SELECT *
FROM yourTable
WHERE colPK IN (SELECT MAX(colPK) FROM yourTable GROUP BY name, address, city, state, zip)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886