[00:02] <StevenK> wgrant: http://pastebin.ubuntu.com/5879216/ without the ORDER BY. It's very strange.
[00:02] <wgrant> StevenK: How's it strange?
[00:03] <StevenK> I guess I'm used to postgres returning unordered data with no ORDER BY, this is always ordered
[00:04] <wgrant> It's not ordered or unordered
[00:04] <wgrant> It's ordered by however it happens to come out
[00:07] <wgrant> So, I would use a subselect to be simple and sure
[00:16] <StevenK> wgrant: So SELECT * FROM (blah..) ORDER BY ... ?
[00:17] <wgrant> StevenK: Other way around
[00:17] <wgrant> You need to do the sort on the inside, group on the outside
[00:17] <wgrant> You don't care about the order of the groups
[00:17] <wgrant> You care about the selection of the group result rows
[00:37] <StevenK> wgrant: http://pastebin.ubuntu.com/5879285/
[01:38] <wgrant> StevenK: That looks substantially more sensible and likely to work
[01:52] <StevenK> wgrant: But they're both the same!
[01:53] <wgrant> ... indeed
[01:54] <wgrant> I guess it's not at all surprising :)
[01:55] <wgrant> GROUP BY with extra columns isn't meant to be permitted
[01:55] <wgrant> Since it doesn't have an obvious meaning
[01:55] <wgrant> StevenK: Rework to use a subquery with GROUP BY and MAX(date), then join the extra columns in the outer query?
[01:55] <wgrant> That has to work
[01:56] <wgrant> Probably slow, but we have a proper solution for prod
[03:16] <StevenK> wgrant: SELECTing what, though?
[03:16] <StevenK> I need to pull out at least one extra column, 'id'
[03:25] <wgrant> StevenK: Why?
[03:25] <wgrant> StevenK: Select the GROUP BY cols and MAX(date)
[03:25] <wgrant> Then join in the outer query, filtering by the GROUP BY cols and date
[03:31] <StevenK> wgrant: http://pastebin.ubuntu.com/5879591/
[03:32] <wgrant> dat caps
[03:32] <wgrant> Why left join?
[03:32] <wgrant> And that join is the wrong way around
[03:32] <StevenK> wgrant: I was guessing
[03:32] <wgrant> And it'll return duplicates... possibly best to do a second group by on the outer layer, I guess
[03:32] <wgrant> Never guess :)
[03:33] <wgrant> This is SQL
[03:33] <wgrant> Not some other thing that you might have to guess about :)
[03:33] <wgrant> LEFT JOIN has a very specific purpoise
[03:33] <StevenK> Yeah
[03:33] <StevenK> It looks right with JOIN
[03:35] <wgrant> I'd hope so
[03:35] <wgrant> Given that it's what you want!
[03:37] <StevenK> wgrant: http://pastebin.ubuntu.com/5879605/
[03:40] <wgrant> StevenK: I'd add a further GROUP BY on the outside to eliminate dupes with the same key and date
[03:42] <StevenK> wgrant: http://pastebin.ubuntu.com/5879610/
[06:04] <StevenK> wgrant: You're happy with that query and I should continue trying to get Django to actually use it?
[06:04] <wgrant> StevenK: That sounds reasonable
[06:05] <StevenK> Which might involve swearing, throwing screwdrivers at the wall, and if I get really desperate -- gin
[06:06] <wgrant> Or ""
[06:06] <StevenK> Not quite that simple, since I'd like to deal with the case where only one of object or operation were specified