0

Suppose I have two tables that both have a created_datetime field. How can I get the MAX(created_datetime), with both of them taken into account. In SQL this is done with a UNION

I would prefer to use Django models to do this, but if need be I can do this with raw SQL.

Ben G
  • 26,091
  • 34
  • 103
  • 170

2 Answers2

1

You can try the following:

max(Model1.objects.aggregate(max_date = Max('created_datetime'))['max_date'], Model2.objects.aggregate(max_date = Max('created_datetime'))['max_date'])

This doesn't do a UNION, but you get what you require.

N.B. : max is a python function and Max a django models function ( from django.db.models.aggregates import Max has to be used for Max).

You can also do something like the following:

max(Model1.objects.all().values_list('created_datetime',flat=True).extend(Model2.objects.all().values_list('created_datetime',flat=True)))

Here, aggregation hasn't been used (and so, the Max not used). max of python has still been used and anything like UNION of sql is still not seen, but, joining of 2 lists has been used.

You can see what suits you bests or some other 3rd solution if any drops by. I would really love to even know a better solution than the above. :)

You could also refer the link to see if it helps you.

Community
  • 1
  • 1
Sandip Agarwal
  • 1,890
  • 5
  • 28
  • 42
  • I'm using your suggestion for the time being, but will leave the question open. One thing for people to beware of with your answer is to validate that both querysets are not empty, otherwise you'll get an error: `can't compare datetime.datetime to NoneType` – Ben G Mar 09 '12 at 19:51
0

It's a bit hard without any other information, but I think you want GREATEST.

MAX operates along rows (ie it turns many rows into one row), and GREATEST operates along columns (turns many columns into one column).

If you want the single biggest created_datetime out of both tables, you'd do:

SELECT GREATEST( MAX(t1.created_datetime), MAX(t2.created_datetime) )
FROM t1
JOIN t2 ON ....
....

The MAX(t1.created_datetime) grabs the max datetime from t1, the MAX(t2.created_datetime) grabs the max from t2, and GREATEST gets the larger of these two.

mathematical.coffee
  • 55,977
  • 11
  • 154
  • 194