Need help removing duplicate entries

[phishbfm]

ive got an output that looks like this:

what i want is if the acct numbers are the same, then I want only the entry with the highest profile number to stay. anyone know how to do this?

 

[jmersing]

Have you tried using "Max" on the profile field, and then "Group by" on the remaining fields?

 

[phishbfm]

that didnt work

 

[Brianwarnock]

I think that this has to be done as 2 queries. If you group on all fields then when accounts are equal but other fields are not then you will still get the output.

One query just selects the account when profile is max, then this joins to the other query to select the records.

Brian

 

[phishbfm]

could you explain this a bit more? im confused

 

[Brianwarnock]

When I came to explain it I realised that your output hasn't got a unique field for the 1st query to output and then join on for the second query uum sorry back to the drawing board.

Brian

 

[Brianwarnock]

Hi
sorry had a senior moment yesterday and lost the plot, it all came back to me last night!

Assume your table is table1 and call your query query2 write a new query1 which just has acct Groupby and Profile Max this then joins on these two fields to table1 as input to query1 which now only selects those records with max profile.

Brian