[Tutor] Question on a select statement with ODBC

[Al Bull]

-----Original Message-----
From: Tutor [mailto:tutor-bounces+a.bull=pubdmgroup.com at python.org] On
Behalf Of Alan Gauld
Sent: Wednesday, October 22, 2014 12:13 PM
To: tutor at python.org
Subject: Re: [Tutor] Question on a select statement with ODBC

On 22/10/14 16:06, Al Bull wrote:
> I don't think I explained the problem properly.     I have several hundred
> thousand records in the ORD table.  There are many instances of records
with
> identical ORD_DBASUB values.   Where duplicates exist, I only want to keep
> the most current record.

Ah, OK thats very different.
You can do it in SQL but it gets messy and depends on the details of the
ODBC SQL, which I don't know... It would involve a nested select with an
inner join I suspect.

> This code works except in very specific cases.  Take the following
example:
>   ORD_DBASUB DATE
> 1) 100000360 2004-11-02
> 2) 100000360 2004-09-03
> 3) 100000334 2004-04-05
> 4) 100000334 2004-03-08
>
> Record #3 is correctly saved, but record #4 is not removed.    It appears
> that ROW is being moved to the next entry after the ord_rows.remove

That's correct you should never modify the collection that you are iterating
over with a for loop.

Instead convert to using a while loop and only increment the index if you
don't remove an thing.

Alternatively make a copy of the collection and iterate over that, but a
while is usually preferable IMHO.

--
Alan G
Author of the Learn to Program web site
http://www.alan-g.me.uk/
http://www.flickr.com/photos/alangauldphotos

_______________________________________________
[Al Bull] 
Quick question then...

Does this do the trick?

Currentrecord = 1

While currentrecord <= len(ord_rows):
      if savedbasub == currentrecord.ord_dbasub:
          ord_rows.remove(currentrecord)
         delcount += 1      
   else:
      savedbasub = currentrecord.ord_dbasub
     currentrecord =+ 1