Skip to content

Instantly share code, notes, and snippets.

@shangxiao
Last active May 17, 2018
Embed
What would you like to do?
CTE's in Django

What's a CTE?

Slides 19-69 of Markus Winand's "Modern SQL":

Sample Problem

If I had a bunch of users with some activity each day, how can I construct a query
to get their last activity object on that day for each of the days?

Consider the following table:

+—————————————————————————————————————————————————————————————————————————————+
|                                 Activity                                    |
+-----------------------------------------------------------------------------|
|  Who?       |   When?   |    What?                                          |
+-------------+-----------+---------------------------------------------------|
|  g1eb       |   9am     |    Go to work                                     |
|  g1eb       |   5pm     |    Knock-off time!                                |
|  shangxiao  |   9am     |    Go to work                                     |
|  shangxiao  |   5pm     |    Knock-off time!                                |
|  shangxiao  |   6pm     |    Attend MelbDjango                              |
+-----------------------------------------------------------------------------+

We want to know what each person's final activity is:

+-----------------------------------------------------------------------------+
|  g1eb       |   5pm     |    Knock-off time!                                |
|  shangxiao  |   6pm     |    Attend MelbDjango                              |
+-----------------------------------------------------------------------------+

http://sqlfiddle.com/#!15/5bc43/2

https://repl.it/@rapilabs/django-cte

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment