Converting SELECT DISTINCT ON queries from Postgresql to MySQL

by

There’s not an exact equivalent to convert a Postgresql query that makes use of SELECT DISTINCT ON to MySQL.

Postgresql SELECT DISTINCT ON

In Postgresql, the following query will eliminate all rows where the expressions (col1, col2, col3) match, and it will only keep the “first col4, col5 row” for each set of matched rows:

SELECT DISTINCT ON (col1, col2, col3) col4, col5
FROM tablename

So if your table is like this:

col1 | col2 | col3 | col4 | col5
--------------------------------
1    | 2    | 3    | 777  | 888
1    | 2    | 3    | 888  | 999
3    | 3    | 3    | 555  | 555

our query will keep just one row for (1,2,3) and one row for (3,3,3). The resulting rows will then be:

col4 | col5
-----------
777  | 888
555  | 555

please notice that the “first row” of each set is unpredictable, our fist row might be (888, 999) as well unless we specify an ORDER BY:

SELECT DISTINCT ON (col1, col2, col3) col4, col5
FROM tablename
ORDER BY col1, col2, col3, col4

(the DISTINCT on expressions must match the leftmost ORDER BY expressions, but the ORDER BY can contain additional expressions).

MySQL extension to GROUP BY

MySQL extends the use of GROUP BY so that we can select nonaggregated columns not named in the GROUP BY clause. Whenever we select nonaggregated columns the server is free to choose any value from each group from that column, so the resulting values will be indetermined.

So this Postgresql query:

SELECT DISTINCT ON (col1, col2, col3) col4, col5
FROM tablename

can be considered equivalent to this MySQL query:

SELECT col4, col5
FROM tablename
GROUP BY col1, col2, col3

both Postgresql and MySQL will return the “First row” for each (col1, col2, col3), and in both cases the row returned is unpredictable because we didn’t specify and order by clause.

A lot of people would be very tempted to convert this Postgresql query with an ORDER BY:

SELECT DISTINCT ON (col1, col2, col3) col4, col5
FROM tablename
ORDER BY col1, col2, col3, col4

with this one:

SELECT col4, col5
FROM (
  SELECT col1, col2, col3, col4, col5
  FROM tablename
  ORDER BY col1, col2, col3, col4
) s
GROUP BY col1, col2, col3

the idea here is to apply an ORDER BY to a subquery so that when MySQL groups by col1, col2, col3 it will keep the first encountered value for col4 and col5. The idea is good, but it’s wrong! MySQL is free to choose any value for col4 and col5, and we don’t know which are the first values encountered, it depends on the optimizer. So I would correct it to this:

SELECT t1.col4, t1.col5
FROM tablename t1 INNER JOIN (SELECT col1, col2, col3, MIN(col4) as m_col4
                              FROM tablename
                              GROUP BY col1, col2, col3) s
     ON t1.col1=s.col1
        AND t1.col2=s.col2
        AND t1.col3=s.col3
        AND t1.col4=s.m_col4
GROUP BY
  t1.col1, t1.col2, t1.col3, t1.col4

but this is starting to get more complicated.

Conclusion

As a general rule, there’s not an exact way to convert a Postgresql query to a MySQL query, but there are a lot of workarounds, the resulting query might be as simple as the original one or it might become very complicated, but it depends on the query itself.

More Related Contents:

  1. Group by clause in mySQL and postgreSQL, why the error in postgreSQL?
  2. What’s faster, SELECT DISTINCT or GROUP BY in MySQL?
  3. SELECT / GROUP BY – segments of time (10 seconds, 30 seconds, etc)
  4. Sum results of a few queries and then find top 5 in SQL
  5. mysql select sum group by date
  6. sql group by versus distinct
  7. What is better subquery vs literal on IN clause in mysql
  8. SQL query return data from multiple tables
  9. How to select the nth row in a SQL database table?
  10. MySQL SELECT only not null values
  11. How to group by week in MySQL?
  12. MySQL SELECT increment counter
  13. Group mysql query by 15 min intervals
  14. Is it possible to GROUP BY multiple columns using MySQL?
  15. Using Count to find the number of occurrences
  16. How does MySQL’s ORDER BY RAND() work?
  17. MySQL #1093 – You can’t specify target table ‘giveaways’ for update in FROM clause
  18. MySQL INSERT INTO … VALUES and SELECT
  19. GROUP BY – do not group NULL
  20. Compare only day and month with date field in mysql
  21. Syntax error when using except in a query
  22. MySQL INNER JOIN select only one row from second table
  23. Using index, using temporary, using filesort – how to fix this?
  24. What is the default MySQL JOIN behaviour, INNER or OUTER?
  25. MySQL SELECT most frequent by group
  26. Insert data into table with result from another select query
  27. How to use Table output from stored MYSQL Procedure
  28. mysql count group by having
  29. Selecting distinct 2 columns combination in mysql
  30. What are the max number of allowable parameters per database provider type?

Leave a Comment