468,242 Members | 1,722 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,242 developers. It's quick & easy.

SQL Query to Select Largest Value

88
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?
Oct 17 '07 #1
2 15639
Rabbit
12,511 Expert Mod 8TB
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:
Expand|Select|Wrap|Line Numbers
  1. DELETE Table1.*
  2. FROM Table1 AS x
  3. WHERE ValueField < (SELECT Max(ValueField) AS MaxOfValueField FROM Table1 WHERE ID = x.ID;);
  4.  
Oct 18 '07 #2
fperri
88
Try this:
Expand|Select|Wrap|Line Numbers
  1. DELETE Table1.*
  2. FROM Table1 AS x
  3. WHERE ValueField < (SELECT Max(ValueField) AS MaxOfValueField FROM Table1 WHERE ID = x.ID;);
  4.  

Thank you, that works great!
Feb 6 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

9 posts views Thread by curwen | last post: by
reply views Thread by Brian Newsham | last post: by
4 posts views Thread by Wanny | last post: by
4 posts views Thread by Subodh | last post: by
2 posts views Thread by Marco | last post: by
5 posts views Thread by =?Utf-8?B?bWljaGFlbCBzb3JlbnM=?= | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by kermitthefrogpy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.