How to Achieve the Functionality of Postgresql's DISTINCT ON in MySQL?

Translating Postgresql's DISTINCT ON Queries to MySQL

Migrating from Postgresql to MySQL often requires adapting existing queries. One such query type is the DISTINCT ON query, which retrieves unique rows while considering multiple columns. While MySQL lacks an exact equivalent, there are approaches to achieve similar functionality.

Postgresql's SELECT DISTINCT ON

Postgresql's SELECT DISTINCT ON query retrieves distinct rows based on specified columns. For instance:

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

This query returns only one row for each unique combination of (col1, col2, col3), keeping the first encountered col4 and col5 values.

MySQL's Group By Extension

MySQL extends the GROUP BY clause to allow selection of non-aggregated columns. However, the specific row returned for each group is indeterminate. This means a query like:

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

is equivalent to Postgresql's DISTINCT ON query in terms of row selection. However, the returned row is not predictable.

Adapting to MySQL

To ensure predictability, an orderly approach is required. Despite the allure of using a subquery with ORDER BY, MySQL's optimizer may not respect it.

A more reliable approach is:

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

This query finds the first encountered row satisfying the DISTINCT ON criteria in Postgresql by using a correlated subquery to retrieve the minimum col4 value for each group.

Conclusion

While Postgresql's DISTINCT ON query does not have a direct counterpart in MySQL, there are workarounds. The choice of approach depends on the specific query and the desired level of complexity.

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