472,952 Members | 2,223 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,952 software developers and data experts.

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 15907
Rabbit
12,516 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

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

Similar topics

9
by: curwen | last post by:
Hi, I'm in deep struggle with query from a huge table: what I've got is a lot of records like: _______________ numb fk1 _______________ 1231456 61 1231456 62
0
by: Brian Newsham | last post by:
------=_NextPart_000_004F_01C352B1.E5B8FA20 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, I'm working on a PHP based website that loads...
4
by: Wanny | last post by:
Hi There, I can't seem to see what's wrong with the query below DELETE FROM Users_Details UD1 WHERE UD1.UserID = ( SELECT TOP 1 UD2.UserID FROM Users_Details UD2 WHERE UD1.useremail =...
5
by: Bernie | last post by:
Greetings, I have 3 servers all running SQL Server 2000 - 8.00.818. Lets call them parent, child1, and child 2. On parent, I create a view called item as follows: CREATE view Item as...
4
by: Subodh | last post by:
I've a table where there are 4 columns for Qty(Q1, Q2, Q3, Q4) and another column as Virtual_Pack. I've to write a query to update Virtual_Pack column. The logic is as follows 1 virtual pack...
2
by: Marco | last post by:
My SELECT query returns a data set, one column of which contains a set of values corresponding to the same date. I.e. for each date in column "Date", I have a set of numbers in column "Numbers"....
4
by: Steve Oster | last post by:
Hi all, I have to create a query using MS Query, and what I need is beyond my knowledge. I am querying from a list of invoices, and each invoice can have several delivery dates (possibly a...
1
by: Rex | last post by:
Hi, I have an Address table which contains more than one addresses for a particular member. I want to write a query that would only display most current address. All addresses have a unique ID...
5
by: =?Utf-8?B?bWljaGFlbCBzb3JlbnM=?= | last post by:
After having played around with LINQ and reading the literature on it I have not been successful in getting this to work so I am looking for some guidance: I want to find a hunk in a collection...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
0
tracyyun
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
2
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
4
NeoPa
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
3
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
0
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.