CODEWITHSUNDEEP
mysqlgroup-bymaxdistinct
Peter Flynn
Peter Flynn

Reputation: 243

Select the whole records with highest value for each group

My CMS stores pages with their past versions in the same table. I want to extract the most recent version of each record by productID. For:

productID version name text  price etc etc
ABC123       1.2  Fred Stuff 2.34  ... ...
DEF456       0.3  Jill Other 1.99  ... ...
ABC123       1.3  Fred Stuff 2.99  ... ...
DEF456       0.4  Jill Other 2.50  ... ...
ABC123       1.4  Fred Stuff 3.45  ... ...

I need to get:

ABC123       1.4  Fred Stuff 3.45  ... ...
DEF456       0.4  Jill Other 2.50  ... ...

(any order). I have experimented with variations on

select distinct(version),* group by productID,max(version) from table;

but I can't figure out how to do it. The other answers for 'select highest' are all about counts and sums; I just need whole records.

Upvotes: 1

Views: 1102

Answers (1)

xQbert
xQbert

Reputation: 35343

Cross Apply would work great, except this is mySQL... so

You need to generate a set of data which contains the logical key for the table and the max value you're interested in. Then join back to the base set.

So SELECT max(version) mv, productID from table group by productID gets us the max version for each productID and then we can join it back to the base set to get the other data needed.

SELECT *
FROM Table A
INNER JOIN (SELECT max(version) mv, productID 
            FROM table 
            GROUP BY productID) B
 on A.ProductID= B.ProductID
and A.Version = B.MV

Upvotes: 3

Related Questions