Selecting value corresponding with MAX value of a group

Question

I am trying to get the OXSEOURL of my OXSEO table.

Structure:
oxobjectid | oxseourl | oxparams

Data:
http://imageshack.com/a/img268/7443/3xr4.png
http://imageshack.com/a/img42/315/8bdu.png
My deepest SEO URL always has the higher value in OXPARAMS field.
Only the numeric values, the others are never count..

Return should be:

http://imageshack.com/a/img29/8404/4jbv.png

I found a solution yesterday, but it was very slow, now I am trying to get a faster way to do it.
So I would like to get the oxseourl for the same oxobjectid with the max oxparams value.
I have more than 330.000 rows, so every ms counts..

I only have to select the urls for products staring with "tbproduct_" objectid.

My query:

SELECT seo2.oxseourl, seo2.oxobjectid, seo2.oxparams
FROM oxseo AS seo2
JOIN (
      SELECT oxobjectid, 
          MAX(oxparams) AS maxparam
      FROM    oxseo
      GROUP BY
          oxobjectid
     ) AS usm
     ON usm.maxparam = seo2.oxparams
WHERE seo2.oxobjectid LIKE '%tbproduct_%'
    AND seo2.oxparams REGEXP '^-?[0-9]+$'

But this returns the same rows for the products.

Thanks for any help.

Answer 1

A bit optimized, and a lot faster:

SELECT seo.oxseourl, seo.oxobjectid, MAX(seo.oxparams)
FROM oxseo AS seo
WHERE seo.oxobjectid LIKE 'tbproduct_%' AND seo.oxparams REGEXP '^-?[0-9]+$'
GROUP BY seo.oxseourl, seo.oxobjectid