SQL Query to Select Largest Value

[fperri]

Hello,

I have a query that creates a table of duplicates in my database. For one set of duplicates (three records), all the fields & values are the same except for the values in one field. This field is a numeric field. I need to figure out a way to remove all the duplicates except for the one with the highest value in that field.

I don't want to have to create a module for this. I want to run it all from a stored query. I was thinking the Max() function but that only returns the max value. Is there any other functions that would pull the highest value out of a set? Or, is there a way you think to do a nested select query where the highest value is grabbed and then used in a comparrison to delete all the records that don't have that value?

I'm sorry if this is confusing. If you don't think its possible in a single query maybe a suggestion for more than one query to do this?

[Rabbit]

Hello,

I have a query that creates a table of duplicates in my database. For one set of duplicates (three records), all the fields & values are the same except for the values in one field. This field is a numeric field. I need to figure out a way to remove all the duplicates except for the one with the highest value in that field.

I don't want to have to create a module for this. I want to run it all from a stored query. I was thinking the Max() function but that only returns the max value. Is there any other functions that would pull the highest value out of a set? Or, is there a way you think to do a nested select query where the highest value is grabbed and then used in a comparrison to delete all the records that don't have that value?

I'm sorry if this is confusing. If you don't think its possible in a single query maybe a suggestion for more than one query to do this?

Try this:

DELETE Table1.*
FROM Table1 AS x
WHERE ValueField < (SELECT Max(ValueField) AS MaxOfValueField FROM Table1 WHERE ID = x.ID;);
 

[fperri]

Try this:

DELETE Table1.*
FROM Table1 AS x
WHERE ValueField < (SELECT Max(ValueField) AS MaxOfValueField FROM Table1 WHERE ID = x.ID;);
 

Thank you, that works great!