How to Replicate Postgresql's SELECT DISTINCT ON in MySQL?

Converting Postgresql's SELECT DISTINCT ON to MySQL

When migrating from Postgresql to MySQL, one may encounter the need to convert queries utilizing Postgresql's SELECT DISTINCT ON statement to their MySQL equivalents.

Postgresql's SELECT DISTINCT ON

In Postgresql, SELECT DISTINCT ON is used to remove duplicate rows based on the unique combination of specified columns. It eliminates all matching rows and retains only the "first" row for each distinct combination.

MySQL Extension to GROUP BY

MySQL does not have an exact counterpart to SELECT DISTINCT ON. Instead, it employs an extension to GROUP BY that allows non-aggregated columns to be selected even if they are not named in the GROUP BY clause. However, the choice of which value from each group is unpredictable. Therefore, we can potentially use GROUP BY in MySQL to achieve a similar effect:

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

This query will return the "first" row for each (col1, col2, col3) combination, but the specific order of these rows is indeterminate.

Maintaining Row Order

To specify the order of rows returned, we can use a subquery:

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

While this approach appears logical, MySQL still has the discretion to select any value for col4 and col5. To ensure the expected row order, we need a more complex query:

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;

Conclusion

While there is no direct translation of Postgresql's SELECT DISTINCT ON to MySQL, workarounds using GROUP BY and subqueries provide the desired functionality. However, the resulting MySQL queries may be more complex than their Postgresql counterparts.

The above is the detailed content of How to Replicate Postgresql's SELECT DISTINCT ON in MySQL?. For more information, please follow other related articles on the PHP Chinese website!