flask-sqlalchemy group_by with max id
<p>I have a table like this
<a href="https://i.stack.imgur.com/6Y6EV.png" rel="nofollow"><img src="https://i.stack.imgur.com/6Y6EV.png" alt="enter image description here"></a></p>

<p>Now I want to get data which ip is uniq and id is max.</p>

<pre><code>In [53]: ModuleInfo.query.group_by(ModuleInfo.ip).all()
Out[53]:
[&lt;ModuleInfo 1&gt;,
 &lt;ModuleInfo 5&gt;,
 &lt;ModuleInfo 14&gt;,
 &lt;ModuleInfo 16&gt;,
 &lt;ModuleInfo 20&gt;,
 &lt;ModuleInfo 17&gt;,
 &lt;ModuleInfo 28&gt;,
 &lt;ModuleInfo 27&gt;]
</code></pre>

<p>retuan values are not max id, I want to <code>ip:127.0.0.1</code>-><code>id:4</code>, I use order_by id, but it just reverse the list.</p>

<p>And when I try to use <code>distinct</code>, I found it not work:</p>

<pre><code>In [51]: ModuleInfo.query.distinct(ModuleInfo.ip).all()
Out[51]:
[&lt;ModuleInfo 1&gt;,
 &lt;ModuleInfo 2&gt;,
 &lt;ModuleInfo 3&gt;,
 &lt;ModuleInfo 4&gt;,
 &lt;ModuleInfo 5&gt;,
 &lt;ModuleInfo 6&gt;,
 &lt;ModuleInfo 7&gt;,
 &lt;ModuleInfo 14&gt;,
 &lt;ModuleInfo 15&gt;,
 &lt;ModuleInfo 16&gt;,
 &lt;ModuleInfo 17&gt;,
 &lt;ModuleInfo 18&gt;,
 &lt;ModuleInfo 19&gt;,
 &lt;ModuleInfo 20&gt;,
 &lt;ModuleInfo 21&gt;,
 &lt;ModuleInfo 22&gt;,
 &lt;ModuleInfo 23&gt;,
 &lt;ModuleInfo 24&gt;,
 &lt;ModuleInfo 25&gt;,
 &lt;ModuleInfo 26&gt;,
 &lt;ModuleInfo 27&gt;,
 &lt;ModuleInfo 28&gt;,
 &lt;ModuleInfo 29&gt;,
 &lt;ModuleInfo 30&gt;]
</code></pre>

<p>You can try this:</p>

<pre><code>from sqlalchemy import func
foo = Session.query(func.max(ModuleInfo.id),ModuleInfo.ip).\
    group_by(ModuleInfo.ip).order_by(ModuleInfo.ip).all()
</code></pre>

<p>I used declarative base and scoped Session to run a test here but the idea is the same. You should receive a list of tuples with (max(id), ip) as a result. </p>

<p>Hannu</p>

python mysql sqlalchemy