Web2py DAL find the record with the latest date
<p>Hi I have a table with the following structure.</p>

<p>Table Name: DOCUMENTS</p>

<p>Sample Table Structure:</p>

<pre><code>ID | UIN      | COMPANY_ID | DOCUMENT_NAME | MODIFIED_ON         |
---|----------|------------|---------------|---------------------|
1  | UIN_TX_1 | 1          | txn_summary   | 2016-09-02 16:02:42 |
2  | UIN_TX_2 | 1          | txn_summary   | 2016-09-02 16:16:56 | 
3  | UIN_AD_3 | 2          | some other doc| 2016-09-02 17:15:43 |
</code></pre>

<p>I want to fetch the latest modified record UIN for the company whose id is 1 and document_name is "txn_summary".</p>

<p>This is the postgresql query that works:</p>

<pre><code>select distinct on (company_id)
       uin
from documents
where comapny_id = 1
and document_name = 'txn_summary'
order by company_id, "modified_on" DESC;
</code></pre>

<p>This query fetches me UIN_TX_2 which is correct.</p>

<p>I am using web2py DAL to get this value. After some research I have been successful to do this:</p>

<pre><code>fmax = db.documents.modified_on.max()
query = (db.documents.company_id==1) &amp; (db.documents.document_name=='txn_summary')

rows = db(query).select(fmax)
</code></pre>

<p>Now "rows" contains only the value of the modified_on date which has maximum value. I want to fetch the record which has the maximum date inside "rows". Please suggest a way. Help is much appreciated.</p>

<p><strong>And my requirement extends to find each such records for each company_id for each document_name.</strong></p>

<p>Your approach will not return complete row, it will only return last modified_on value.</p>

<p>To fetch last modified record for the company whose id is 1 and document_name "txn_summary", query will be</p>

<pre><code>query = (db.documents.company_id==1) &amp; (db.documents.document_name=='txn_summary')
row = db(query).select(db.documents.ALL, orderby=~db.documents.modified_on, limitby=(0, 1)).first()
</code></pre>

<p><code>orderby=~db.documents.modified_on</code> will return records arranged in descending order of <code>modified_on</code> (last modified record will be first) and <code>first()</code> will select the first record. i.e. complete query will return last modified record having company 1 and document_name = "txn_summary".</p>

<p>There can be other/better way to achieve this. Hope this helps!</p>

python postgresql web2py data-access-layer