MySQL - Control which row is returned by a group by

Question

I have a database table like this:

id    version_id    field1    field2
1     1             texta      text1
1     2             textb      text2
2     1             textc      text3
2     2             textd      text4
2     3             texte      text5

If you didn't work it out, it contains a number of versions of a row, and then some text data.

I want to query it and return the version with the highest number for each id. (so the second and last rows only in the above).

I've tried using group by whilst ordering by version_id DESC - but it seems to order after its grouped, so this doesn't work.

Anyone got any ideas? I can't believe it can't be done!

UPDATE:

Come up with this, which works, but uses a subquery:

SELECT *
FROM (SELECT * FROM table ORDER BY version_id DESC) t1
GROUP BY t1.id

Answer 1

It's called selecting the group-wise maximum of a column. Here are several different approaches for mysql.

Here's how I would do it:

SELECT *
FROM (SELECT id, max(version_id) as version_id FROM table GROUP BY id) t1
INNER JOIN table t2 on t2.id=t1.id and t1.version_id=t2.version_id

This will be relatively efficient, though mysql will create a temporary table in memory for the subquery. I assume you already have an index on (id, version_id) for this table.

It's a deficiency in SQL that you more or less have to use a subquery for this type of problem (semi-joins are another example).

Subqueries are not well optimized in mysql but uncorrelated subqueries aren't so bad as long as they aren't so enormous that they will get written to disk rather than memory. Given that in this query only has two ints the subquery could be millions of rows long before that happened but the select * subquery in your first query could suffer from this problem much sooner.

Answer 2

I think this would do it, not sure if it is the best or fastest though.

SELECT * FROM table 
WHERE (id, version_id) IN 
  (SELECT id, MAX(version_id) FROM table GROUP BY id)

Answer 3

SELECT id, version_id, field1, field2
FROM (
    SELECT @prev = id AS st, (@prev := id), m.*
    FROM (
           (SELECT @prev := NULL) p,
           (
            SELECT *
            FROM   mytable
            ORDER BY
                   id DESC, version_id DESC
           ) m
     ) m2
WHERE NOT IFNULL(st, FALSE);

No subqueries, one pass on UNIQUE INDEX ON MYTABLE (id, version_id) if you have one (which I think you should)

Answer 4

This query will do the job without a group by:

SELECT * FROM table AS t
LEFT JOIN table AS t2 
    ON t.id=t2.id 
    AND t.version_id < t2.version_id
WHERE t2.id IS NULL

It does not need any temporary tables.

Answer 5

One can always go for analytical functions as well which will give you more control

select tmp.* from ( select id,version_id,field1,field2, rank() over(partition by id order by version_id desc ) as rnk from table) tmp where tmp.rnk=1

If you face issue with rank() function depending on the type of data then one can choose from row_number() or dense_rank() too.

Answer 6

This is pseudo code but something like this should work just fine

select *
from table
inner join
(
    select id , max(version_id) maxVersion
    from table 
) dvtbl ON id = dvtbl.id && versionid = dvtbl.maxVersion

Answer 7

I usually do this with a subquery:

select id, version_id, field1, field2 from datatable as dt where id = (select id from datatable where id = dt.id order by version_id desc limit 1)

Answer 8

I think this is what you want.

select id, max(v_id), field1, field2 from table group by id

The results I get from that are

1, 2, textb, text2

2, 3, texte, text5

Edit: I recreated the table and insert the same data with the id an version_id being a compound primary key. This gave the answer I provided earlier. It was also in MySQL.

Answer 9

not tested it but something like this might work:

SELECT * FROM table GROUP BY id ORDER BY MAX(version_id) DESC