Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old August 14th, 2003, 09:43 AM
Authorized User
 
Join Date: Aug 2003
Location: , , .
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
Default Duplicates

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.
  #2 (permalink)  
Old August 14th, 2003, 10:09 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
  #3 (permalink)  
Old August 14th, 2003, 10:43 AM
Authorized User
 
Join Date: Aug 2003
Location: , , .
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

  #4 (permalink)  
Old August 14th, 2003, 10:47 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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


  #5 (permalink)  
Old August 14th, 2003, 10:56 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
  #6 (permalink)  
Old August 14th, 2003, 10:59 AM
Authorized User
 
Join Date: Aug 2003
Location: , , .
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

  #7 (permalink)  
Old August 14th, 2003, 11:04 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
  #8 (permalink)  
Old August 14th, 2003, 11:11 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
  #9 (permalink)  
Old August 14th, 2003, 11:36 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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

  #10 (permalink)  
Old August 14th, 2003, 12:21 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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




Similar Threads
Thread Thread Starter Forum Replies Last Post
Removing duplicates hewstone999 Access VBA 2 November 4th, 2008 05:26 PM
Yes to duplicates MMJiggy62 Beginning VB 6 3 July 14th, 2006 11:51 AM
Checking for duplicates toedipper2 SQL Server 2000 3 June 7th, 2006 03:50 PM
Duplicates in XSLT d_sathish XSLT 1 November 9th, 2005 11:45 AM
Delete duplicates? boson SQL Language 2 August 11th, 2004 04:47 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.