On Sep 14, 2011, at 7:19 AM, Jani Tiainen wrote:
> 14.9.2011 12:46, Jonas H. kirjoitti:
>> On 09/14/2011 11:37 AM, Иван Иванов wrote:
>>> The problem here is, like Peter said, that you cannot order before
>>> grouping. And that's very annoying.
>>
>> Of course you can, using a subselect just like I showed. SQLite example:
>>
>> > .schema
>> CREATE TABLE a (name varchar, mod int);
>>
>> e> select * from a;
>> b1|1
>> b1|2
>> b1|3
>>
>> > select * from a group by name;
>> b1|3
>>
>> > select * from (select * from a order by mod desc) group by name;
>> b1|1
>>
>
> Problem is that is not standard SQL.
>
> In standard implemetation group by _requires_ aggregation function that is
> applied to groups so you won't be able to get "last" that way.
>
> It might work non-standard way in some implementations. And thus Django
> usually follows standard or smallest common nominator for db backends such a
> thing is not possible to support.
I didn’t know you could do that in SQLite. I know it doesn’t work in MySQL or
in PostgreSQL.
The proper ANSI SQL way of doing this is “SELECT name, MIN(mod) FROM a GROUP BY
name” and the Django way is
“Model.objects.values('name').annotate(Min('mod'))”. But that only works on a
table with two fields. If there is more data that you want to include, you’ll
have to get raw. The SQL will look like “SELECT * FROM a AS t1 WHERE mod =
(SELECT MIN(mod) FROM a AS t2 WHERE t2.name = t1.name))”. Finding out the exact
details depends on the specifics of your queryset.
https://docs.djangoproject.com/en/1.3/topics/db/aggregation/
Peter of the Norse
rahmc...@radio1190.org
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en.
