Think of it this way: A JOIN combines tables in a specified way and (logically) creates a new table that contains the combined stuff.

The PostgreSQL LIMIT clause is used to limit the data amount returned by the SELECT statement.

The LIMIT clause offers users the ability to set a hard maximum on the number of records to retrieve. The GROUP BY clause helps you summarize data by grouping together records that have one or more column values in common. SELECT c.identifier, p.persons FROM class c CROSS JOIN LATERAL ( SELECT ARRAY ( SELECT identifier FROM person WHERE class_identifier = c.identifier ORDER BY name DESC LIMIT 2 ) AS persons ) p ORDER BY c.identifier You also don't need GROUP BY in the outer SELECT this way. If both OFFSET and LIMIT appear, then OFFSET rows are skipped before starting to count the LIMIT rows that are returned. OFFSET 0 is the same as omitting the OFFSET clause. OFFSET says to skip that many rows before beginning to return rows. LIMIT ALL is the same as omitting the LIMIT clause. PostgreSQL GROUP BY clause examples Let’s take a look at the payment table in the sample database. PostgreSQL evaluates the GROUP BY clause after the FROM and WHERE clauses and before the HAVING SELECT, DISTINCT, ORDER BY and LIMIT clauses. It’s possible to use other clauses of the SELECT statement with the GROUP BY clause. Or have the best selling categories in an e-commerce website showing the 3 most popular products in those categories.

For example showing all the new posts with the the two latest comments on them. In web applications it’s very common to try to limit the results by group.

Postgresql where in