Finding which row has max 1

[sjean233]

I am trying to find which row has the result produced by MAX(). I've tried quite a few different approaches, but I'm not getting what I want. Here is a simplified example:

Table: prod_ver
id   category  version
1    5         3
22   52        3
88   5         4
116  52        5
117  3         2
118  41        1
120  52        2

SELECT p.id, p.category, MAX(version)
FROM prod_ver AS p
WHERE p.cat IN (5,52)
GROUP BY p.category;

This produces:
1 5 4
22 52 5

What I'd like to see is:
88 5 4
116 52 5

How do I get the row that is associated with the MAX found for each category?

Thanks.

 

[hvass]

You need a subquery

http://dev.mysql.com/doc/refman/5.0/en/subqueries.html

This assumes you are not using a pre 4.1 version. If you are pre 4.1 or you cannot get the subquery to perform fast then you need to do the same thing but manually in 3 steps in the 3 step shuffle - give us a shout if you need to do this

 

[sjean233]

This is what I finially got to work. Is there a better way to do this? I'll add a few more details to explain why I have some of the stuff at the end. Oh, and I'm using MySQL v4.1.12 so I do have subqueries.

SELECT p.id, p.category, CONCAT_WS('.', IFNULL(p.major, 0),
                                        IFNULL(p.minor)) AS fullver
FROM prod_ver AS p
LEFT JOIN (SELECT p2.category, MAX(CONCAT_WS('.', 
                                   IFNULL(p2.major, 0),  
                                   IFNULL(p2.minor))) AS big
           FROM prod_ver AS p2
           WHERE p2.category IN (5,52)
           GROUP BY p2.category) AS x ON p.category=x.category
WHERE p.category=x.cateogry
HAVING fullver=x.big;

 

[hvass]

subquery approach

step 1 - you want the max version for each category you are interested in

select category, max(vers) as vers from test.t1 where category in (5,52) group by category;
********************* 2 rows **************************
category vers
5 4
52 5

*******************************************

step 2 - for these cases you want the complete record from the original table so you would write something like

select
*
from test.t1
where ....

This is the form of the subquery that returns multiple rows

http://dev.mysql.com/doc/refman/5.0/en/row-subqueries.html

You put the two together

*******************************************
MySQL: Tue Nov 28 18:01:52 2006 Command Run=
select
*
from test.t1
where (category,vers) in (select category, max(vers) as vers from test.t1 where category in (5,52) group by category);
********************* 2 rows **************************
id category vers
88 5 4
116 52 5

*******************************************

 

[sjean233]

Thanks, I didn't realize you could compare pairs of items using the IN statement. That makes for a much better solution.

> where (category,vers)