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
