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

[Phlip]

Does anyone have a QuerySet for that?

( BTW please don't try to talk me out of it; I've been doing SQL since
1989 and am fully aware of all the alternatives there. C-; )

[ankit rai]

say your model name is Student, and it has a field names as marks

query set will be

Student.objects.all().aggregate(Max('marks')
this will be give you max marks

st=Student.objects.filter(marks__in=Student.objects.all().aggregate(Max('marks')))

--ankit

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To post to this group, send email to django...@googlegroups.com.
To unsubscribe from this group, send email to django-users...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-users?hl=en.

[Tom Evans]

I'm curious, why can't I talk you into
Student.objects.all().order_by('-score')[0] ?

It is clearly a superior query :/

Cheers

Tom

[Piotr Kilczuk]

2010/10/26 Tom Evans <tevans.uk@googlemail.com>

I'm curious, why can't I talk you into
Student.objects.all().order_by('-score')[0] ?

It is clearly a superior query :/

This would select only one row; there can possibly be multiple students with a top note.

Am I right? :)

Regards,
Piotr 

[Tom Evans]

Course there could be, I'm being dense :/

On Tue, Oct 26, 2010 at 10:55 AM, Piotr Kilczuk <kil...@gmail.com> wrote:
>
>
> 2010/10/26 Tom Evans <teva...@googlemail.com>

>>
>> I'm curious, why can't I talk you into
>> Student.objects.all().order_by('-score')[0] ?
>>
>> It is clearly a superior query :/
>
> This would select only one row; there can possibly be multiple students with
> a top note.
>
> Am I right? :)
>
> Regards,
> Piotr
>

[Phlip]

On Oct 26, 2:42 am, Tom Evans <tevans...@googlemail.com> wrote:

> I'm curious, why can't I talk you into
> Student.objects.all().order_by('-score')[0] ?
>
> It is clearly a superior query :/

> >> ( BTW please don't try to talk me out of it; I've been doing SQL since
> >> 1989 and am fully aware of all the alternatives there. C-; )

Hmm. Maybe I ought to start another thread where I describe the actual
problem, in nauseatingly elaborate detail. In this thread I just want
to learn any sick notations available there.

Other mails not processed yet. BRB!

[Phlip]

> st=Student.objects.filter(marks__in=Student.objects.all().aggregate(Max('ma rks')))

Aha - a marks__in may point to an aggregate subquery.

In conclusion, screw my SQL server's optimizer. It deserves to suffer!

(I can't seem to find a self-join to do what I need either...)

[Tom Evans]

Phlip, I'm going to try and make a non-stupid comment now :)

If you already know precisely the query you want to use, and you can't
coerce django's ORM to produce it, can you simply use Manager.raw()[1]
to generate the result set you are after?

Eg,

Student.objects.raw(r'SELECT * FROM `student` WHERE mark=(select
max(mark) from student)')

Cheers

Tom

[1] http://docs.djangoproject.com/en/1.2/topics/db/sql/

[Phlip]

Tom Evans wrote:

> Phlip, I'm going to try and make a non-stupid comment now :)

http://xkcd.com/386/

> If you already know precisely the query you want to use, and you can't
> coerce django's ORM to produce it, can you simply use Manager.raw()[1]
> to generate the result set you are after?

Because the point of an ORM is to distribute declarations of relations
among objects, so each object adds details to a query set, and the ORM
can build the final SELECT statements for each context.

So anyway, here's a table (hand-censored - it's a blue-sky project in
a hyper-competitive space):

> select * from things;
+-----+-----------------+
| pid | name |
+-----+-----------------+
| 6 | soca_2k7_user |
| 7 | isabelle_item |
| 8 | max_item |
| 9 | isabelle_item |
+-----+-----------------+

Note that "isabelle_item" appears twice. We are following the auditing
rule "always write new records to change data - never edit previous
records". Someone edited isabelle_item's payload data (not shown), so
we add a new record without touching the existing record.

This implies that all normal database queries should only look at the
"top level horizon" of the database. (And this implies we must mix-and-
match such queries, and they can't all rely on the order_by('-pk')[0]
trick.)

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).

--
Phlip
http://zeekland.zeroplayer.com/

[Steve Holden]

On 10/26/2010 12:40 PM, Phlip wrote:
> Note that "isabelle_item" appears twice. We are following the auditing
> rule "always write new records to change data - never edit previous
> records". Someone edited isabelle_item's payload data (not shown), so
> we add a new record without touching the existing record.

I hope the auditors are only forcing you to do this with records that
aren't referenced as part of relationships, otherwise your database is
going to get hammered updating all the foreign keys.

Wouldn't it make more sense (not that auditors will necessarily be
persuaded by sensible arguments) to dump a copy of a row (plus possibly
a timestamp field) to an archival table before update? This coild easily
be done on a pre-save signal ...

regards
Steve
--
DjangoCon US 2010 September 7-9 http://djangocon.us/

[Phlip]

> I hope the auditors are only forcing you to do this with records that
> aren't referenced as part of relationships, otherwise your database is
> going to get hammered updating all the foreign keys.

The design spec (which is ours, not any "CPA auditor's"), say to
duplicate the living crap out of them. Plz don't go there.

> Wouldn't it make more sense (not that auditors will necessarily be
> persuaded by sensible arguments) to dump a copy of a row (plus possibly
> a timestamp field) to an archival table before update? This coild easily
> be done on a pre-save signal ...

Maybe.

Now how do I do a "group by" on an aggregate? I'm down to this:

st=Student.objects.filter(marks__in=Student.objects.all().aggregate(Max('marks'),
group_by='name'))

Guess what? The "group_by" doesn't work, and Googling for "group by"
returns all kinds of useless newb crap. Just sayin...

[Łukasz Rekucki]

This sounds like what django-reversion[1] does :)

[1]: http://github.com/etianen/django-reversion#readme

--
Łukasz Rekucki

[Phlip]

> This sounds like what django-reversion[1] does :)
>
> [1]:http://github.com/etianen/django-reversion#readme

We have to cover the situation where some clients might still have
rev(n-1), while some are up-to-date with rev(n). So we _probably_ need
the history in the same table as the current version.

I'm aware this is borderline "big requirements up front", but the
answer turns out to be...

class ThingManager(models.Manager):

def get_query_set(self):
qs = super(ThingManager, self).get_query_set()
max_pids = QuerySet(self.model, using=self._db)
max_pids =
max_pids.values('name').annotate(Max('pid')).values('pid')
return qs.filter(pid__in=max_pids)

Now we can write any ORM statement we can think of, and (if those
lines continue to pass tests) then we only see the top horizon of the
data. Unless we need to go deeper.

Thanks, all!

--
Phlip

[Alec Shaner]

On Tue, Oct 26, 2010 at 12:40 PM, Phlip <phli...@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.

[Phlip]

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

I didn't do values_list because I guessed that the inner query would
run and produce an array, then the outer query would run.

My way, with values() on both sides of the aggregate(), MIGHT insert
the inner SELECT statement into the outer one, like our SQL examples.

[Phlip]

On Oct 26, 10:24 am, Phlip <phlip2...@gmail.com> wrote:
> > This sounds like what django-reversion[1] does :)
>
> > [1]:http://github.com/etianen/django-reversion#readme
>
> We have to cover the situation where some clients might still have
> rev(n-1), while some are up-to-date with rev(n). So we _probably_ need
> the history in the same table as the current version.
>
> I'm aware this is borderline "big requirements up front", but the
> answer turns out to be...
>
> class ThingManager(models.Manager):
>
>     def get_query_set(self):
>         qs = super(ThingManager, self).get_query_set()
>         max_pids = QuerySet(self.model, using=self._db)
>         max_pids =
> max_pids.values('name').annotate(Max('pid')).values('pid')

Uh, that was

        max_pids =
max_pids.values('name').annotate(max_id=Max('pid')).values('pid')

If you don't name it you don't get the Max, even though nobody uses
the max_id in the resulting SELECT statement:

SELECT "things".*,
FROM "things"
WHERE ("things"."pid" IN (
SELECT MAX(U0."pid") AS "max_id"
FROM "things" U0
GROUP BY U0."name" ))