How can I GROUP BY one column, while sorting only by another.
I'm trying to do the following:
SELECT dbId,retreivalTime
FROM FileItems
WHERE sourceSite='something'
GROUP BY seriesName
ORDER BY retreivalTime DESC
LIMIT 100
OFFSET 0;
I want to select the last /n/ items from FileItems, in descending order, with the rows filtered by DISTINCT values of seriesName. The above query errors out ERROR: column "fileitems.dbid" must appear in the GROUP BY clause or be used in an aggregate function. I need the dbid value in order to then take the output of this query, and JOIN it on the source table to get the rest of the columns I wasn.
Note this is basically the gestalt of the below question, with a lot of the extraneous details removed for clarity.
Original question
I have a system I'm migrating from sqlite3 to PostgreSQL, because I've largely outgrown sqlite:
SELECT
d.dbId,
d.dlState,
d.sourceSite,
[snip a bunch of rows]
d.note
FROM FileItems AS d
JOIN
( SELECT dbId
FROM FileItems
WHERE sourceSite='{something}'
GROUP BY seriesName
ORDER BY MAX(retreivalTime) DESC
LIMIT 100
OFFSET 0
) AS di
ON di.dbId = d.dbId
ORDER BY d.retreivalTime DESC;
Basically, I want to select the last n DISTINCT items in the database, where the distinct constraint is on one column, and the sorting order is on a different column.
Unfortunately, the above query, while it works fine in sqlite, errors out in PostgreSQL with the error psycopg2.ProgrammingError: column "fileitems.dbid" must appear in the GROUP BY clause or be used in an aggregate function.
Unfortunately, while adding dbId to the GROUP BY clause fixes the issue (e.g. GROUP BY seriesName,dbId), it means the distinct filtering on the query results no longer work, since dbid is the database primary key, and as such all values are distinct.
From reading the Postgres documentation, there is SELECT DISTINCT ON ({nnn}), but that requires that the returned results be sorted by {nnn}.
Therefore, to do what I'd want via SELECT DISTINCT ON, I'd have to query for all DISTINCT {nnn} and their MAX(retreivalTime), sort again by retreivalTime rather then {nnn}, then take the largest 100 and query using those against the table to get the rest of the rows, which I'd like to avoid, as the database has ~175K rows and ~14K distinct values in the seriesName column, I only want the latest 100, and this query is somewhat performance critical (I need query times < 1/2 second).
My naive assumption here is basically the DB needs to just iterate over each row in descending order of retreivalTime, and simply stop once it's seen LIMIT items, so a full table query is non-ideal, but I don't pretend to really understand how the database system optimizes internally, and I may be approaching this completely wrong.
FWIW, I do occasionally use different OFFSET values, but long query times for cases where offset > ~500 is completely acceptable. Basically, OFFSET is a crappy paging mechanism that lets me get away without needing to dedicate scrolling cursors to each connection, and I'll probably revisit it at some point.
Ref - Question I asked a month ago that lead to this query.
Ok, more notes:
SELECT
d.dbId,
d.dlState,
d.sourceSite,
[snip a bunch of rows]
d.note
FROM FileItems AS d
JOIN
( SELECT seriesName, MAX(retreivalTime) AS max_retreivalTime
FROM FileItems
WHERE sourceSite='{something}'
GROUP BY seriesName
ORDER BY max_retreivalTime DESC
LIMIT %s
OFFSET %s
) AS di
ON di.seriesName = d.seriesName AND di.max_retreivalTime = d.retreivalTime
ORDER BY d.retreivalTime DESC;
Works correctly for the query as described, but if I remove the GROUP BY clause, it fails (it's optional in my application).
psycopg2.ProgrammingError: column "FileItems.seriesname" must appear in the GROUP BY clause or be used in an aggregate function
I think I'm fundamentally not understanding how subqueries work in PostgreSQL. Where am I going wrong? I was under the impression that a subquery is basically just a inline function, where the results are just fed into the main query.
group by + join rows data, see stackoverflow.com/questions/12558509/…