[Tutor] Question on a select statement with ODBC

[Alan Gauld]

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