CODEWITHSUNDEEP
mysql
user892134
user892134

Reputation: 3214

Mysql JOIN query MAX value

This is example of the problem i am having. The query should return rows paul and rick because they have the highest rating of the child rows. Instead the query is returning dave and owen, my guess is because they are the first child rows. I am grouping by position and using MAX(child.rating) but the query isn't working like i want it to be. In the real table i have alot of columns that is why i use child.* in the select clause.

mytable

  id |  name | parentid|  position| rating  |
   1 |  mike |    1    |    1     |    6    |
   2 |  dave |    1    |    2     |    5    |
   3 |  paul |    1    |    2     |    7    |
   4 |  john |    1    |    2     |    3    |
   5 |  mike |    5    |    1     |    8    |
   6 |  owen |    5    |    2     |    2    |
   7 |  rick |    5    |    2     |    9    |
   8 |  jaye |    5    |    2     |    3    |



$getquery = mysql_query("SELECT MAX(child.rating),child.* FROM mytable child 
        LEFT JOIN mytable parent on parent.parentid=child.parentid 
        WHERE parent.name LIKE '%mike%' GROUP BY child.position,child.parentid");



while($row=mysql_fetch_assoc($getquery)) {

 $id = $row['id'];
 $name = $row['name'];
 $parentid = $row['parentid'];



if($id==$parentid) {
    continue;
    }
echo "<p>Name: $name </p>";

}

Upvotes: 0

Views: 4851

Answers (5)

Mukesh
Mukesh

Reputation: 73

add:

ORDER BY child.rating DESC

Upvotes: 0

Slava
Slava

Reputation: 2050

This must be what you're trying to do (although I'm unsure if're really comparing child's parentid with parent's parentid):

SELECT child.* FROM mytable child 
    INNER JOIN mytable parent on parent.parentid=child.parentid 
    LEFT JOIN mytable child2 ON (child2.parentid = parent.parentid AND child2.position = child.position AND child2.rating > child.rating)
    WHERE parent.name LIKE '%mike%' AND child2.parentid IS NULL
    GROUP BY child.position, child.parentid
    HAVING `better` = 0;

Another option would be to use a subquery, but you should check which works faster:

SELECT child.*
FROM (
    SELECT MAX(child.rating) maxrating, child.parentid, child.position FROM mytable child 
        INNER JOIN mytable parent on parent.parentid=child.parentid 
        WHERE parent.name LIKE '%mike%'
        GROUP BY child.position,child.parentid
) h
    INNER JOIN mytable child ON (child.parentid = h.parentid AND child.position = h.position AND child.rating = h.maxrating)

performance may be very different on tables of different sizes.

If I haven't got your point right, I still suggest you use INNER JOINs instead of OUTERs if you don't need anything for which there's nothing to join. INNER JOINs are usually way faster.

I actually think second one will work faster on larger tables.

Upvotes: 0

Mosty Mostacho
Mosty Mostacho

Reputation: 43494

Funny thing, I've just realized what you're looking for. Here is the final query:

select t1.* from mytable t1
left join mytable t2
on t1.parentid = t2.parentid and t1.rating < t2.rating
join mytable parents
on parents.id = t1.parentid
where t2.rating is null and parents.name like '%mike%'

And here is a working example

Upvotes: 1

slaakso
slaakso

Reputation: 9080

You can use a subquery in from clause to first figure out what is the maximum rating for each parent and then get the children with that rating:

select * 
from mytable c
join 
   (select parentid, max(rating) as 'maxrating'
    from mytable m
    group by parentid) as q on c.parentid=q.parentid and c.rating = q.maxrating;

Upvotes: 3

Ben
Ben

Reputation: 3972

This is the way mysql's group by works, and is actually working correctly.

There are two way around it, either a subquery or joins that get the top most child, and you probably want to reorder the way your tables are

Here's the join method (if i'm understanding your data correctly):

SELECT child.* 
FROM mytable parent 
LEFT JOIN mytable child ON parent.parentid=child.parentid 
LEFT JOIN mytable child2 ON child.parentid=child2.parentid AND child2.rating > child.rating AND child2.parentid IS NULL 
WHERE parent.name LIKE '%mike%' AND parent.position = 1 AND child.position <> 1

This makes the assumption that parants always have a position of 1, and children do not. You may need to also add another bit to the child2 join to remove the possibility of parents having a higher rating than the children?

The 2nd join makes sure there are no other children with a higher rating for each parent.

Upvotes: 0

Related Questions