Also be aware that there's a gotcha when using WHERE abc NOT IN (SELECT xyz FROM ...) that has bitten me a few times in the past... 😁 Perhaps not an issue in this scenario because it's an ID and likely NOT NULL, but if the select subquery ever returns a NULL value for xyz the condition evaluates to false and may give you unexpected results. WHERE abc IN (SELECT xyz FROM ...) doesn't have the same problem, it effectively ignores the NULLs. cheers, Tony On 17/11/2022 16:24, Alan Ingleby via ozdotnet wrote:
If the ID is unique across all records,
SELECT * FROM <tablename> WHERE ID NOT IN (SELECT MAX(ID) FROM <tablename>GROUP BY NAme,Desc,Date,Etc)
On Thu, 17 Nov 2022 at 16:02, Tom P via ozdotnet <ozdotnet@ozdotnet.com> wrote:
Apologies if this is basic for probably most of you but I just can't get my head around it.
I have a flat table in sql server which contains lots of duplicates, differing only by one column.
Id,Name,Desc,Date,Etc 1,abc,abc abc,2022-11-17,a 2,abc,abc abc,2022-11-17,a 5,def,def def,2022-11-17,a 4,abc,abc abc,2022-11-17,a 3,def,def def,2022-11-17,a 6,xyz,def def,2022-11-17,a
I'm trying to write a query that finds all duplicates _excluding the ones with the highest Id_. So for the above example it would return the following:
Id,Name,Desc,Date,Etc 1,abc,abc abc,2022-11-17,a 2,abc,abc abc,2022-11-17,a 3,def,def def,2022-11-17,a
There are many millions of rows to process so looking for something efficient. Any advice would be appreciated.
Regards Tom
-- ozdotnet mailing list To manage your subscription, access archives: https://codify.mailman3.com/
-- Alan Ingleby