1

Suppose I have a table in db with rows like this:

ID Foo
19 1
20 1
38 2
44 1
50 2
61 1

I want to get max id (50 in this example) from queryset like this:

MyModel.objects.filter(foo=2).order_by('id').values_list('id')[:limit]

limit, of course, can be greater than total row number. I can do this by fetching all queryset, convert it to list and use the last item

list(MyModel.objects.filter(foo=2).order_by('id').values_list('id')[:limit])[-1]

but can I do this in database, without fetching entire queryset?

Raw sql solution is welcome too. DB is PostgreSQL.

Update:

I need max id of items inside limited set, not all table! For example if limit == 1 if will be 38, if limit == 2 it will be 50 and if limit == 500 it still will be 50.

lampslave
  • 1,431
  • 1
  • 15
  • 20
  • Do you mean to the the max *after* an arbitrary `LIMIT`? Because otherwise, `LIMIT` stops making sense once you only want the max `id`. Well, `LIMIT` without deterministic `ORDER BY` is odd (and often questionable) to begin with. Clarify please. – Erwin Brandstetter May 14 '23 at 00:04
  • @lampslave: the limit itself makes not much sense: a database table has no inherent order. So querying it might return records in any order, for example 38 might be first in one query, but second in another one, etc. – Willem Van Onsem May 14 '23 at 12:48
  • Thank you about order by, I knew it, but did not specify. I updated the question. – lampslave May 14 '23 at 12:52

2 Answers2

1

You can just use:

from django.db.models import Max

MyModel.objects.filter(foo=2).aggregate(max=Max('id'))['max']

this will make a single query that will only return the greatest id for all MyModels with foo=2.

As for getting the n-th, you can work with a union queryset, like:

qs = MyModel.objects.filter(foo=2)
list(
    qs.order_by('id')[limit - 1 : limit].union(qs.order_by('-id')[:1], all=True)
)[0].id

This will retrieve at most two records, one with the limit, and the last one. We then cast these to a list and return the first of the two. If the first one with the limit is missing, we get the last one (the second queryset). But these are retrieved in the same database query.

Willem Van Onsem
  • 443,496
  • 30
  • 428
  • 555
  • I need max id of rows inside limited set, not all table. – lampslave May 14 '23 at 12:53
  • Great! Or even (I guess) qs.order_by('id')[limit - 1 : limit].union(qs.order_by('-id')[:1], all=True).first(), check if first is None (because table can be empty) and get id from it. Thank you a lot, I'll test this soon and accept your answer. – lampslave May 14 '23 at 13:06
0

If all you need is the max id, it's very basic:

SELECT max(id) AS id
FROM   tbl
WHERE  foo = 2;

If you need more columns or the whole row:

SELECT *
FROM   tbl
WHERE  foo = 2
ORDER  BY id DESC
LIMIT  1;

IF id can be null, you'll probably want:

...
ORDER  BY id DESC NULLS LAST
...

See:

If the table is big and performance matters, a multicolumn index on (foo, id DESC) would be ideal. (Or (foo, id DESC NULLS LAST) if id can be null.) Just on (foo, id) is almost as good. And if there are only few rows for the same foo, just on (foo) is typically good enough.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228