Tricky MS Access SQL query to remove redundant duplicate records

Question

I have an Access form table (I simplify it a bit)

ID            AutoNumber       Primary Key
SchemeName    Text (50)
SchemeNumber  Text (15)

It contains some data, for example ...

ID            SchemeName           SchemeNumber
--------------------------------------------------------------------
714           Malcolm              ABC123
80            Malcolm              ABC123
96            Malcolms Scheme      ABC123
101           Malcolms Scheme      ABC123
98            Malcolms Scheme      DEF888
654           Another Scheme       BAR876
543           Whatever Scheme      KJL111
etc...

Now. I want to remove duplicate names under the same SchemeNumber. But I want to leave an entry with the longest SchemeName for this schema number. If there are duplicate entries with the same long length, I just want to leave only one, say, the lowest identifier (but anyone will do it really). In the above example, I would like to remove the identifiers 714, 80, and 101 (leaving only 96).

I thought it would be relatively easy to achieve, but it would turn into a nightmare! Thanks for any suggestions. I know that I could program it cyclically, but I would prefer one DELETE request.

Answer 1

, , :

SELECT r.SchemeNumber, r.SchemeName, Min(r.ID) AS MinOfID
FROM
    (SELECT
        SchemeNumber,
        SchemeName,
        Len(SchemeName) AS name_length,
        ID
    FROM tblSchemes
    ) AS r
    INNER JOIN
    (SELECT
        SchemeNumber,
        Max(Len(SchemeName)) AS name_length
    FROM tblSchemes
    GROUP BY SchemeNumber
    ) AS w
    ON
        (r.SchemeNumber = w.SchemeNumber)
        AND (r.name_length = w.name_length)
GROUP BY r.SchemeNumber, r.SchemeName
ORDER BY r.SchemeName;

, qrySchemes2Keep. DELETE tblSchemes, ID qrySchemes2Keep.

DELETE 
FROM tblSchemes AS s
WHERE Not Exists (SELECT * FROM qrySchemes2Keep WHERE MinOfID = s.ID);

, Access DELETE, "" SQL :

DELETE s.*, Exists (SELECT * FROM qrySchemes2Keep WHERE MinOfID = s.ID)
FROM tblSchemes AS s
WHERE (((Exists (SELECT * FROM qrySchemes2Keep WHERE MinOfID = s.ID))=False));

Answer 2

DELETE FROM Table t1
WHERE EXISTS (SELECT 1 from Table t2
             WHERE t1.SchemeNumber = t2.SchemeNumber
             AND Length(t2.SchemeName) > Length(t1.SchemeName)
)

, (Oracle - length, mysql - length, sql server - LEN)

Answer 3

delete ShortScheme
from Scheme ShortScheme
join Scheme LongScheme
  on ShortScheme.SchemeNumber = LongScheme.SchemeNumber
  and (len(ShortScheme.SchemeName) < len(LongScheme.SchemeName) or (len(ShortScheme.SchemeName) = len(LongScheme.SchemeName) and ShortScheme.ID > LongScheme.ID))

( SQL Server)

, . , , : , , , .

Answer 4

. , , - , ? sql 'undo'.

-- Setup the data
DROP Table foo;
DROP Table bar;
DROP Table bat;
DROP Table baz;
CREATE TABLE foo (
  id int(11) NOT NULL,
  SchemeName varchar(50),
  SchemeNumber varchar(15),
  PRIMARY KEY (id)
);

insert into foo values (714, 'Malcolm', 'ABC123' );
insert into foo values (80, 'Malcolm', 'ABC123' );
insert into foo values (96, 'Malcolms Scheme', 'ABC123' );
insert into foo values (101, 'Malcolms Scheme', 'ABC123' );
insert into foo values (98, 'Malcolms Scheme', 'DEF888' );
insert into foo values (654, 'Another Scheme ', 'BAR876' );
insert into foo values (543, 'Whatever Scheme ', 'KJL111' );

-- Find all the records that have dups, find the longest one
create table bar as
    select max(length(SchemeName)) as max_length, SchemeNumber
    from foo
    group by SchemeNumber
    having count(*) > 1;

-- Find the one we want to keep
create table bat as
    select min(a.id) as id, a.SchemeNumber
    from foo a join bar b on a.SchemeNumber = b.SchemeNumber 
       and length(a.SchemeName) = b.max_length
    group by SchemeNumber;

-- Select into this table all the rows to delete
create table baz as 
    select a.id from foo a join bat b where a.SchemeNumber = b.SchemeNumber 
      and a.id != b.id;

, .

, , . , , , , . .

, , .

delete from foo where id in (select id from baz);

- , , , , . , , - , , .

Answer 5

:

   Select * From Table t
   Where Len(SchemeName) <
      (Select Max(Len(Schemename))
       From Table
       Where SchemeNumber = t.SchemeNumber )
    And Id > 
      (Select Min (Id) 
       From Table
       Where SchemeNumber = t.SchemeNumber
           And SchemeName = t.SchemeName)

:,...

   Select * From Table t
   Where Id > 
      (Select Min(Id) From Table
       Where SchemeNumber = t.SchemeNumber
         And Len(SchemeName) <
            (Select Max(Len(Schemename))
             From Table
             Where SchemeNumber = t.SchemeNumber))

, ,

   Delete 
   From Table t
   Where Len(SchemeName) <
      (Select Max(Len(Schemename))
       From Table
       Where SchemeNumber = t.SchemeNumber )
    And Id > 
      (Select Min (Id) 
       From Table
       Where SchemeNumber = t.SchemeNumber
           And SchemeName = t.SchemeName)

:

 Delete From Table t Where Id > 
  (Select Min(Id) From Table
   Where SchemeNumber = t.SchemeNumber
     And Len(SchemeName) <
        (Select Max(Len(Schemename))
         From Table
         Where SchemeNumber = t.SchemeNumber))

Answer 6

If your platform supports ranking functions and common table expressions:

with cte as (
  select row_number() 
     over (partition by SchemeNumber order by len(SchemeName) desc) as rn
  from Table)
delete from cte where rn > 1;