Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #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.
Reply With Quote
  #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
Reply With Quote
  #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.

Reply With Quote
  #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


Reply With Quote
  #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
Reply With Quote
  #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.

Reply With Quote
  #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
Reply With Quote
  #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
Reply With Quote
  #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

Reply With Quote
  #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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 06:52 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.