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?
2  15951   Rabbit 12,516
Recognized Expert Moderator MVP
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;);
-
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!
Sign in to post your reply or Sign up for a free account.
Similar topics | |
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
|
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 custom pricing for users =
where they logon.
|
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 = UD2.useremail
|
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
select * from child1.dbchild1.dbo.Item union all
select * from child2.DBChild2.dbo.Item
|
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 consists of Q1 = 10, Q2 = 2, Q3 = 3.5, Q4 = 6
Lets say Q1 = 20, Q2 = 2, Q3 = 20, Q4 = 6 then the Virtual_Pack will
be = 1 because Q2 = 2
Example Q1 = 30, Q2 = 5, Q3 = 8, Q4 = 15 then the Virtual_Pack will be
| | |
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". However, I am
only interested in getting the largest value in column "Numbers"
corresponding to each value in column "Date". How do I do that?
Thanks,
Marco
|
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 different date for each line item on the
invoice).
I need to retrieve the largest delivery date for each invoice that does not
|
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 (addID).
Example:
memberID addID address1
-------- ------ --------------------------------------------------
295 69 13 Auster St
|
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 whose Start field is the largest in
the collection but less than an arbitrary cutoff ("selectedRow"). The result
of the query should just be that single object of type Hunk. (No two objects
will have the same value for their Start fields.) Here is...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
