Translate query with GROUP BY from MySQL to Postgres and SQLite

Question

In MySQL I use a lot of constructs which look like this:

LEFT JOIN(
    SELECT field_one, field_two, field_three FROM (
        SELECT field_one, field_two, field_three FROM table_one
        ORDER BY field_three 
    ) o GROUP BY field_one
) abc ON cur_table.some_field = abc.field_one

I use it, because it helps me to implement a usual task - to order some data before grouping. Now I wonder how to migrate this SQL construct from MySQL to SQLite and PostgreSQL.

Answer 1

I suspect the purpose of your query is to use the MySQL quirk to pick an arbitrary row for un-aggregated columns that are also not listed in the existing GROUP BY clause (which is against the SQL standard and not supported by most other RDBMS). By ordering in a subquery you make MySQL pick the row with the smallest field_three per gropup, so I'll assume you want:

the smallest field_three for every field_one, and field_two from the same row to go with it.

Your original query does not work in Postgres which follows the SQL standard here. If a SELECT has a GROUP BY clause, all output columns have to be listed or be aggregated. Consider:

• How do I port query with GROUP BY clause to PostgreSQL?

Standard SQL with window function

One possible standard-SQL solution would be with the window function row_number() in a subquery:

SELECT field_one, field_two, field_three
FROM  (
   SELECT field_one, field_two, field_three
        , row_number() OVER(PARTITION BY field_one ORDER BY field_three) rn
   FROM   table_one
   ) sub
WHERE sub.rn = 1

Works in Postgres, but not in SQLite or MySQL which do not support window functions, yet.

Basic standard SQL

This query works in all three RDBMS (and almost anywhere else, too), but requires unique maxima in field_three (returns multiple rows if there are ties for a maximum field_three per field_one).

SELECT t1.*
FROM   table_one t1
LEFT   JOIN table_one t2 ON t1.field_one = t2.field_one
                        AND t1.field_three < t2.field_three
WHERE  t2.field_one IS NULL

You can resolve ties if you have any unique (set of) column(s), but it's unwieldy. Related:

• Fetch the row which has the Max value for a column

Postgres

(Besides supporting all standard SQL solutions) Postgres also has the powerful DISTINCT ON (an extension of the standard DISTINCT, but not against the standard like the MySQL and SQLite quirks):

SELECT DISTINCT ON (field_one)
       field_one, field_two, field_three
FROM   table_one
ORDER  BY field_one, field_three

You can resolve ties by adding more columns to the ORDER BY. Details:

• Select first row in each GROUP BY group?

SQLite

... has a similar quirk as MySQL (violating the SQL standard). From the release notes:

Queries of the form: "SELECT max(x), y FROM table" returns the value of y on the same row that contains the maximum x value.

So:

SELECT field_one, field_two, max(field_three) AS field_three
FROM   table_one
GROUP  BY field_one

field_two is taken from the row of max(field_three). Related:

• How can I select the set of rows where each item has the greatest timestamp?

---

Join and join condition work the same everywhere:

LEFT JOIN (SELECT ...) abc ON cur_table.some_field = abc.field_one