Re: SELECT * FROM `student` WHERE mark=(select max(mark) from student)

On Tue, Oct 26, 2010 at 12:40 PM, Phlip <phlip2005@gmail.com> wrote:
>
> So this statement correctly fetches only the latest items:
>
> SELECT a.* FROM things a WHERE a.pid in (select max(b.pid) from
> content_entity b group by b.name)
>
> Now I thought (from my allegedly copious experience with SQL) that I
> could do it with a join-on-self, but I can't seem to get the SQL
> syntax right. And if I did, I would then not know how to ORM-ize that
> syntax (and yes it must be ORM-ized, because this is indeed the core
> of the project, and everything has to see top-level horizons. Except
> auditors).
>

Regarding this query, I think you may be able to do this using
annotate. See http://docs.djangoproject.com/en/dev/topics/db/aggregation/#values

For example (and this probably sucks for performance):

Things.objects.filter(id__in=Things.objects.values('name').annotate(max_id=Max('id')).values_list('max_id',
flat=True))

This is just a self join example, but it could probably be rewritten
to use the two tables in your example.

--
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+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-users?hl=en.