SELECT with unique values based on a subset of columns for MySQL, H2 and DB2

user_6930724 0
Viewed 246

I have the following query which works fine in MySQL:

SELECT *
FROM mytable
GROUP BY col2, col3, col4, col5, col6

that allows me to select all results from mytable that are unique (distinct) based on the values of multiple columns col2, col3, col4, col5, col6.

Unfortunately that query does not work for H2 and DB2 (it gives the ...must be in the GROUP BY list... error) and I'm looking for a generic and also efficient alternative.

I have also tried the following one:

SELECT t1.col1, t1.col2, t1.col3, t1.col4, t1.col5, t1.col6
FROM mytable t1
JOIN (SELECT t2.col2, t2.col3, t2.col4, t2.col5, t2.col6
        FROM mytable t2
        GROUP BY t2.col2, t2.col3, t2.col4, t2.col5, t2.col6) x 
        ON x.col1 = t1.col1
            AND x.col2 = t1.col2
            AND x.col3 = t1.col3
            AND x.col4 = t1.col4
            AND x.col5 = t1.col5
            AND x.col6 = t1.col6;

but it was VERY slow (tried with a Docker MariaDB local instance), especially because I will use that SELECT inside a INSERT INTO ... query so I need something efficient.

Any help please?

Thanks

h2 db2 mysql sql
1 Answers

This does not work fine in MySQL:

SELECT *
FROM mytable
GROUP BY col2, col3, col4, col5, col6

Older versions of MySQL happen to support this syntax, but it is broken syntax. The SELECT and GROUP BY clauses are not consistent. Happily MySQL has not supported this syntax (using the default configuration) in versions released in the past few years.

If you want one row per combination, then use window functions or some similar method:

select t.*
from (select t.*,
             row_number() over (partition by col2, col3, col4, col5, col6 order by col2) as seqnum
      from mytable t
     ) t
where seqnum = 1;
user_11440350
Related