display max value when 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?

 

[kickstart]

Hi

 

You will probably need to use a subselect to get the relevant sub rows.

 

Ie, something like (and this will be wrong as I am not 100% clear on what you want):-

 

SELECT *
FROM outcomes parent
INNER JOIN outcomes child ON parent.id=child.sid
INNER JOIN (SELECT nid, MAX(id) AS ChildId FROM outcomes GROUP BY nid) ChildMax ON child.nid = ChildMax.nid
INNER JOIN outcomes child2 ON ChildMax.ChildId = child2.id

 

Ie, find the max id you are interested in, grouped by the relevant field, and then join that back against the table to get the other fields from that row.

 

While you are getting the highest rated child id, that is probably not certain (ie, the row is probably random).

 

However I can't give any more info as your example SQL is only half there

 

All the best

 

Keith