SELECT rows, but remove rows that have duplicate column

Question

I believe this is a very simple query to make, but for some reason it doesn't work in Postgres (it works in other dbs)

This is how db schema kooks like:

tableA(
   id integer,
   ...
)

tableB(
   id integer
   tableA_id integer -- foreign key to tableA
   ...
)

And I want to select records for tableB, join with tableA, then group by tableA_id:

SELECT * from tableB b INNER JOIN tableA ON b.tableA_id = tableA.id
GROUP BY tableA_id
LIMIT 20

Works fine everywhere except Postgres, which throws that infamous "id must appear in group by or be used in aggragate function" error. But if I put b.id into group by then I don't get the duplicate records removed...

Answer 1

Note you should always include the full table schema and example data, as it'll make your questions clearer.

In general, to include fields not in the GROUP BY clause in the SELECT list, you should be using an aggregate function, like so:

SELECT
    b.tableA_id,
    MAX(b.id) AS max_b_id -- Choose whichever aggregate function makes sense here
from tableB b 
INNER JOIN tableA 
    ON b.tableA_id = tableA.id
GROUP BY b.tableA_id
LIMIT 20

This will allow you to return values from the ungrouped columns, and your GROUP BY clause will still de-dupe the records the same as it was before. Note you have to choose which aggregate function makes the most sense for the ungrouped column you want to SELECT. Above I chose the MAX() function as an example, which will just return the largest b.id in this case. If you want the lowest b.id then use the MIN() function, etc.

Without knowing the rest of the fields in your tables or seeing sample data, I'm not sure what the goal of your query is. It'll also return a nondeterminsitic (random) 20 rows whenever you run it because you've specified a LIMIT without an ORDER BY clause.

Also, please don't use SELECT *, it is an anti-pattern. Always explicitly list out only the columns you need from the query. That is how you were receiving the error of your question, because * causes it to try to SELECT ungrouped columns.

Perhaps you don't even want to return the b.id column, and by explicitly listing out the columns names, you don't even need to include it. (Again, not sure what the goal of your query is?) So you could remove it from my example query, if that's the case.

Answer 2

I believe this is a very simple query to make, but for some reason it doesn't work in Postgres (it works in other dbs)

SELECT * 
FROM tableB b INNER JOIN tableA ON b.tableA_id = tableA.id
GROUP BY tableA_id
LIMIT 20 ;

This is incorrect. The statement as is will NOT work correctly in any SQL DBMS that has implemented standard SQL correctly. Postgres, SQL Server, Oracle, Firebird, DB2, event recent versions of MySQL will all reject the statement with similar error messages.

The only two exceptions (I know) are (old versions of) MySQL and SQLite.

Current versions of MySQL (with default settings) will reject the statement with a message similar to Postgres. Old versions of MySQL (5.6 and older, with default settings) will accept the statement and return results but they might be incorrect. There will be one row returned per values of the grouped column (tableAid) but is NO guarantee that the values will be from the same rows. It's possible thus to get results that do not exist in the tables!

In SQLite, the situation is a bit better. The statement is accepted and the result are consistent, coming from the same (one row per group). Details in Aggregate Queries Can Contain Non-Aggregate Result Columns That Are Not In The GROUP BY Clause . One problem is of course that this behaviour is non-standard SQL and you can't expect for this kind of queries to run as is in other DBMS. In fact you should expect that they will be rejected in all other DBMS and will need to be corrected.

For Postgres you are in luck because SQLite's implementation of this non-standard behaviour is exactly what Postgres (non-standard) DISTINCT ON does. So the specific query will be equivalent to the following. You can also use ORDER BY to affect which rows will be selected per group to be included in the results:

SELECT DISTINCT ON (tableA_id)
    * 
FROM tableB b INNER JOIN tableA ON b.tableA_id = tableA.id
-- ORDER BY tableA_id, ...
LIMIT 20 ;