title: Django Using order_by with .annotate() and getting related field
body: <p>I have the following data,</p>
  
  <p><a href="https://i.stack.imgur.com/guCkO.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/guCkO.png" alt="enter image description here"></a></p>
  
  <p>This query groups by topicid, and then in each group gets the max date, frequency of posts and counts the number of authors as contributors,</p>
  
  <pre><code>    info_model = InfoModel.objects.values('topicid')
              .annotate( max=Max('date'), freq=Count('postid'),                   
               contributors=Count('author', distinct=True))
  </code></pre>
  
  <p>This query can then be displayed as follows,</p>
  
  <p><a href="https://i.stack.imgur.com/BmXHx.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/BmXHx.png" alt="enter image description here"></a></p>
  
  <p>Q.1 (SOLVED) How can I order the rows by date, from most recent down? I did appended .order_by('date') to the query, which seems like the most obvious solution but this produces,</p>
  
  <p><a href="https://i.stack.imgur.com/AG0du.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/AG0du.png" alt="enter image description here"></a></p>
  
  <p>completely changing the 'freq' and 'contributions'.</p>
  
  <p>EDIT: The ordering can be achieved by appending .order_by('-max')</p>
  
  <p>Q.2 How can I display the 'post' for that date? So the post column should display,</p>
  
  <p>ya</p>
  
  <p>see ya</p>
  
  <p>ciao</p>
  
  <p>yoyo</p>
  
  <p>I thought the following should work with {{ item.post }}, but no such luck.</p>
  
  <pre><code>  &lt;table class='table table-striped table-hover'&gt;
          &lt;thead&gt;
            &lt;tr&gt;
              &lt;th&gt;freq&lt;/th&gt;
              &lt;th&gt;topicid&lt;/th&gt;
              &lt;th&gt;date&lt;/th&gt;
              &lt;th&gt;contributors&lt;/th&gt;
              &lt;th&gt;post&lt;/th&gt;
            &lt;/tr&gt;
          &lt;/thead&gt;
          &lt;tbody&gt;
            {% for item in info %}
            &lt;tr&gt;
              &lt;td&gt;{{ item.freq }}&lt;/td&gt;
              &lt;td&gt;{{ item.topicid }}&lt;/td&gt;
              &lt;td&gt;{{ item.max }}&lt;/td&gt;
              &lt;td&gt;{{ item.contributors }}&lt;/td&gt;
              &lt;td&gt;{{ item.post }}&lt;/td&gt;
            &lt;/tr&gt;
            {% endfor %}
          &lt;/tbody&gt;
        &lt;/table&gt;
  </code></pre>
  
  <p>Thanks,</p>
  
  <p>Edit:</p>
  
  <p>I can get the correct result with raw SQL but can't do it wth a Django query,</p>
  
  <pre><code>info_model = list(InfoModel.objects.raw('SELECT *, 
                max(date),  
                count(postid) AS freq,     
                count(DISTINCT author) AS contributors FROM        
                crudapp_infomodel GROUP BY topicid ORDER BY date DESC'))
  </code></pre>
  
  <p>I simplified and reposted this problem here <a href="https://stackoverflow.com/questions/37901376/rewrite-raw-sql-as-django-query">Rewrite raw SQL as Django query</a></p>