Member Avatar for 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?

Edited by MDanz because: n/a
  • 4 Contributors
  • 3 Replies
  • 651 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by Biiim

Dani AI

Generated

Short answer for : you need to pick the MAX(rating) per nid and then return the full row that matches that max. As pointed out, selecting child.* together with a GROUP BY gives indeterminate non-aggregated columns. Two reliable patterns follow.

A portable solution (works on older MySQL): compute the max rating per nid in a derived table, then join back to the main table to get the complete row(s) that have that rating.

SELECT o.*
FROM outcomes o
JOIN (
  SELECT nid, MAX(rating) AS max_rating
  FROM outcomes
  GROUP BY nid
) m ON o.nid = m.nid AND o.rating = m.max_rating
JOIN outcomes parent ON parent.id = o.sid
WHERE parent.name LIKE :search
ORDER BY o.rating DESC;

If you run MySQL 8+ you can use window functions to make this simpler and deterministic (add a tie-breaker in the ORDER BY if you want a single row when ratings tie):

SELECT *
FROM (
  SELECT o.*, ROW_NUMBER() OVER (PARTITION BY nid ORDER BY rating DESC, id DESC) AS rn
  FROM outcomes o
  JOIN outcomes parent ON parent.id = o.sid
  WHERE parent.name LIKE :search
) t
WHERE rn = 1
ORDER BY rating DESC;

Notes and cautions:

  • ’s “group by everything” approach just returns distinct combinations (it won’t pick the single best row).
  • ’s HAVING trick can appear to work in MySQL’s nonstandard grouping mode but is ambiguous and fragile—prefer the derived-join or window approach.
  • If you want a single row per nid when multiple rows share the same max rating, add a tie-breaker (e.g., highest id).
  • For speed, index (nid, rating) and the join key; LIKE '%term%' with a leading wildcard won’t use a normal index — consider full‑text or parameterized queries.
  • Always parameterize the search input instead of interpolating $search to avoid SQL injection.

Recommended Answers

All 3 Replies

Member Avatar for 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.

Member Avatar for 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 :)

Member Avatar for 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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.