display row of MAX value of Group By

[MDanz]

Ok, as easiest as i can explain.

- There are many child.sid to parent.id
- There are many child.nid to child.sid
- There are many child.id to child.nid

child.id is just the id of a child row. And each row has rating column.

In the query i'm grouping by child.nid. But the results that are being returned are the first entry(lowest child.id) for each nid. What i want is the highest rated child.id for that nid.

$construct =  "SELECT child.* FROM outcomes child 
    JOIN outcomes parent on parent.id=child.sid JOIN    
    WHERE (parent.name LIKE '%$search%') GROUP BY child.nid,child.sid
    ORDER BY child.rating DESC";

I've tried ORDER BY child.rating DESC but this needs to happen before the GROUP BY child.nid.

Any idea how to solve this problem?

[smantscheff]

A common error is the mixing of aggregating and non-aggregating functions in grouped selects. The select clause "child.*" will select all columns from child, and those which are not part of the "group by" clause will have arbitrary values. MySQL seems to return the values of the first row which it encounters for all non-aggregate columns. Therefore you will have to explicitly

SELECT child.nid, child.sid, max(rating) ...

to get the desired results.

[StephNicolaou]

Select all the fields you want to use and place MAX() around the field you want. Then you have to group by everything youve selected and order by a field ID.

SELECT TBL.field1, MAX(TBL.field2)
FROM TBL
WHERE TBL.childid on TBL.childid and
(parent.name LIKE '%$search%') GROUP BY field1, field2ID ORDER BY field2ID

Have to dash. Hope that helps :)

[Biiim]

Hows this?

GROUP BY child.nid,child.sid
HAVING child.rating = MAX(child.rating)
ORDER BY MAX(child.rating) DESC

It pulls the lot then drops everything thats not the highest child.rating for the group and then orders highest to lowest