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
