How to Convert PostgreSQL's SELECT DISTINCT ON Queries to MySQL?

Converting DISTINCT ON Queries from PostgreSQL to MySQL

In the transition from PostgreSQL to MySQL, users may encounter a difference in the syntax for eliminating duplicate rows based on specific column values. PostgreSQL employs the SELECT DISTINCT ON statement, while MySQL utilizes an extension of the GROUP BY clause.

PostgreSQL's SELECT DISTINCT ON

The SELECT DISTINCT ON (col1, col2, col3) query in PostgreSQL retains the first qualifying row for each unique combination of the specified columns. For example, consider the following table:

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

The query will output:

col4	col5
777	888
555	555

MySQL's Extension to GROUP BY

MySQL's extension allows the selection of non-aggregated columns even if they are not included in the GROUP BY clause. However, the server has the freedom to choose arbitrary values from these columns for each group.

Equivalent MySQL Query

The equivalent MySQL query for the PostgreSQL example is:

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

Both PostgreSQL and MySQL will return one row for each (col1, col2, col3) combination, but the specific row returned may vary due to the lack of an explicit ordering.

Workaround for Ordered Queries

To mimic PostgreSQL's ordered DISTINCT ON behavior, a more complex query in MySQL is required:

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 MySQL does not have an exact counterpart for Postgresql's SELECT DISTINCT ON, workarounds involving the GROUP BY extension can provide similar functionality. The complexity of the workaround may vary depending on the query requirements.

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