Duplicates

ashley_y · August 14th, 2003, 09:43 AM

I have a database with three fields in it: code_version, code_id, and code_description. For each code_id, there may be 1 to 5 different code versions, having the values, 1,2,3,4, or 5. Most codes have version 5, but not all. What I want to do, is take each code and remove the duplicates, i.e. take one code_id, and pair it with the largest code_version code_description.

Jeff Mason · August 14th, 2003, 10:09 AM

Your specification is not clear.

Do you want to delete the rows which are less than the maximum version for each code_id, thereby leaving only the maximum code_version for each code_id (and its associated description)?

If so, then:

Code:

    DELETE YourTable
    WHERE code_version <(SELECT MAX(code_version) FROM YourTable T1 WHERE t1.code_id=YourTable.code_id);

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

ashley_y · August 14th, 2003, 10:43 AM

What I want to do is keep the code_description and code_id, matching it to the largest code_version for that particular code_id.
For instance,

let's say I have this in the table:

code_id: 444 code_version: 1 code_description: desc1
code_id: 444 code_version: 2 code_description: desc1
code_id: 444 code_version: 3 code_description: desc11
code_id: 444 code_version: 4 code_description: desc11
code_id: 444 code_version: 5 code_description: desc12

code_id: 445 code_version: 1 code_description: desc22
code_id: 445 code_version: 2 code_descriptiOn: desc23

I want a query that will leave me with JUST this output:

code_id: 444 code_version: 5 code_description: desc12
code_id: 445 code_version: 2 code_description: desc23

It pulls the largest pair values for each set.

Bob Bedell · August 14th, 2003, 10:47 AM

Hi ashly,

Use a correlated subquery:

DELETE Table1
FROM Table1 AS t1
WHERE code_version Not In (select max(code_version) from Table1 t2 where t1.code_ID=t2.Code_id);

HTH,
Bob

Jeff Mason · August 14th, 2003, 10:56 AM

OK, But it still isn't clear from your narrative 'specification' whether you want to delete the duplicates as you've defined them (which my query will do) or whether you just want to select them. If the latter, then try:

Code:

    SELECT code_id, code_version, code_description
        FROM YourTable T0
    WHERE code_version =(SELECT MAX(code_version) FROM YourTable T1
            WHERE T1.code_id=T0.code_id)

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

ashley_y · August 14th, 2003, 10:59 AM

Yes, I want to DELETE the duplicates and leave only the output as I showed above.

Jeff Mason · August 14th, 2003, 11:04 AM

Quote:

quote:Originally posted by Bob Bedell
DELETE Table1
FROM Table1 AS t1
WHERE code_version Not In (select max(code_version) from Table1 t2 where t1.code_ID=t2.Code_id);

The "DELETE table FROM <table> ... " statement is a SQL Server T/SQL extension which I don't believe is legal in Access.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Jeff Mason · August 14th, 2003, 11:11 AM

Quote:

quote:Originally posted by ashley_y
Yes, I want to DELETE the duplicates and leave only the output as I showed above.

Then, as I described above, this will do it:

Code:

    DELETE YourTable
        WHERE code_version <(SELECT MAX(code_version) FROM YourTable T1 WHERE t1.code_id=YourTable.code_id);

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Bob Bedell · August 14th, 2003, 11:36 AM

Quote:

quote:Originally posted by Jeff Mason

The "DELETE table FROM <table> ... " statement is a SQL Server T/SQL extension which I don't believe is legal in Access.

Quite legal actually. Works fine.

Bob

Jeff Mason · August 14th, 2003, 12:21 PM

Fascinating. I tried your query and you are, of course, correct. My apologies.

But, looking at the Access help for the DELETE statement, I see that the syntax is:

   DELETE [table.*]
   FROM table
   WHERE criteria

and no mention is made, that I can find, of the syntax:

   DELETE table
   FROM table
   WHERE criteria

which is why I made my statement. A legal DELETE statement in SQL-92 can only take the form:

   DELETE [ FROM ] table_name
   WHERE criteria ...

SQL Server adds a proprietary extension to this as:

   DELETE table
   FROM <table source>...
   WHERE criteria ...

allowing a JOIN or other derived table to specify the rows to be deleted.

Your (correct) subquery follows the SQL Server extension syntax, and does not appear to follow legal Access syntax, but I suppose one shouldn't argue with success, since yours works.

Once again, I am hoist by Access's rather loose interpretation of exactly what SQL is...

I'll go away now. :D

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com