-----Original Message----- From: Tutor [mailto:tutor-bounces+a.bull=pubdmgroup....@python.org] On Behalf Of Alan Gauld Sent: Tuesday, October 21, 2014 6:42 PM To: tutor@python.org Subject: Re: [Tutor] Question on a select statement with ODBC
On 21/10/14 19:57, Al Bull wrote: > have multiple records per ord_dbasub. Is there a way I can structure > the select statement to retrieve only the most current record (based > on ord_date)? Yes, the cursor can be told to only retrieve N records, in your case 1. SELECT ord_dbasub, ord_pub,ord_date,ord_service, ... FROM ord WHERE ord_pub='QWKFIL' ORDER BY ord_dbasub, ord_date LIMIT 1 If the sort order is wrong you can specify ASC or DESC to reverse it as needed. > ord_rows = cursor.execute("select ord_dbasub, ord_pub, > ord_date,ord_service," > "ord_agency, ord_woa, ord_status," > "ord_channel, ord_source, ord_giftcomp," > "ord_cnreason " > "from ord " > "Where ord_pub='QWKFIL'" > "order by ord_dbasub, ord_date").fetchall() Rather than all those quotes you can use triple quotes: ord_rows = cursor.execute('''select ord_dbasub, ord_pub, ord_date,ord_service, ord_agency, ord_woa, ord_status, etc... order by ord_dbasub, ord_date limit 1''').fetchall() > for row in ord_rows: > print (row.ord_dbasub, row.ord_date, row.ord_pub) > # Add code here to find the most recent order per DBASUB and > delete other orders If it's in order you could just access the first row using an index. print (ord_rows[0]) > I have to admit that the concept of tuples & dictionaries has me a little > bit confused. I'm used to working with arrays and arrays of structures. tuples are just read-only lists, which, in turn, are arrays that can hold any data type. tuples are also like records without named fields. You can use a named tuple from the collections module which is even more like a record. -- Alan G Author of the Learn to Program web site http://www.alan-g.me.uk/ http://www.flickr.com/photos/alangauldphotos _______________________________________________ Tutor maillist - Tutor@python.org To unsubscribe or change subscription options: https://mail.python.org/mailman/listinfo/tutor [Al Bull] 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. There could be 1-20 or more records with the same ORD_DBASUB value. I am close to having this working. I added the following statement: ord_rows.reverse() to reverse the order of the table. Now for each ORD_DBASUB, the most current record will be the first one. I then added this code: savedbasub = 0 for row in ord_rows: if savedbasub == row.ord_dbasub: ord_rows.remove(row) delcount += 1 else: savedbasub = row.ord_dbasub 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 #1 is saved, as it should be. Record #2 is correctly removed. 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 statement, then being moved again at the top of the loop causing me to drop down into the else clause for record #4. Al Bull, Chief Technology Officer/Owner Publishers Data Management Group a.b...@pubdmgroup.com 815-732-5297 _______________________________________________ Tutor maillist - Tutor@python.org To unsubscribe or change subscription options: https://mail.python.org/mailman/listinfo/tutor