9/15/2023 0 Comments Sqlite order by vs group bySince we are dealing with a big table here, we'd need a query that can use an index, which is not the case for the above query (except for WHERE sourceSite = 'mk') Unquoted mixed case identifiers are a common source of confusion in Postgres. Select first row in each GROUP BY group?Īside: should probably be retrievalTime, or better yet: retrieval_time.ORDER BY seriesName, retreivaltime DESC NULLS LAST - latest retreivaltime To get consistent rows, use DISTINCT ON and wrap it in a subquery to order the result differently: SELECT * FROM ( Your answer does the latter, you combine the maximum dbid with the maximum retreivaltime, which may come from a different row. The important question which does not seem to be on your radar yet:įrom each set of rows for the same seriesName, do you want the columns of one row, or just any values from multiple rows (which may or may not come from the same row)? 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. I think I'm fundamentally not understanding how subqueries work in PostgreSQL. Psycopg2.ProgrammingError: column "riesname" must appear in the GROUP BY clause or be used in an aggregate function Works correctly for the query as described, but if I remove the GROUP BY clause, it fails (it's optional in my application). ON di.seriesName = d.seriesName AND di.max_retreivalTime = d.retreivalTime ( SELECT seriesName, MAX(retreivalTime) AS max_retreivalTime Ref - Question I asked a month ago that lead to this query. 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. 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.įrom reading the Postgres documentation, there is SELECT DISTINCT ON (, 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 ~500 is completely acceptable. Unfortunately, while adding dbId to the GROUP BY clause fixes the issue (e.g. 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. I have a system I'm migrating from sqlite3 to PostgreSQL, because I've largely outgrown sqlite: SELECTīasically, 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. Note this is basically the gestalt of the below question, with a lot of the extraneous details removed for clarity. 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. The above query errors out ERROR: column "fileitems.dbid" must appear in the GROUP BY clause or be used in an aggregate function. I want to select the last /n/ items from FileItems, in descending order, with the rows filtered by DISTINCT values of seriesName. I'm trying to do the following: SELECT dbId,retreivalTime How can I GROUP BY one column, while sorting only by another.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |