CODEWITHSUNDEEP
mysqljoin
htoip
htoip

Reputation: 439

Use MAX only on rows which match ON criteria

How can I modify the following query to restrict the addition from choice to only one row per nid, the one with the highest value in the field foo? That is, there are multiple rows in the choice table with the same nid, and I want the highest foo of each nid to be joined to node.

SELECT *
FROM `node`
LEFT JOIN `choice` ON node.nid = choice.nid

Upvotes: 1

Views: 263

Answers (2)

vyegorov
vyegorov

Reputation: 22905

SELECT *
  FROM node
  LEFT JOIN choice c USING (nid)
 WHERE (c.nid, c.foo) IN (SELECT nid, max(foo) FROM choice GROUP BY nid);

Try it out on SQLFiddle.

EDIT:

Speaking bout adding bar into the list.

In the subquery I'm finding MAX(foo) for each nid, therefore GROUP BY nid. There's no logic in adding bar as it is, you should either use an aggregate on that column or include it into the GROUP BY. It is MySQL “extension” (which I personally find error prone), that allows you to make such things. I assume, that MySQL does MAX(bar) behind the scenes. If you'll run query:

mysql> SELECT nid, max(foo), max(bar) FROM choice GROUP BY nid;
+------+----------+------+
| nid  | max(foo) | bar  |
+------+----------+------+
|    1 |        3 | Uno  |
|    2 |        1 | Eins |
+------+----------+------+

you'll see, that MAX(foo) and MAX(bar) are taken from different rows. Compare the output with the following:

SELECT nid, max(foo), bar FROM choice GROUP BY nid;

As soon as nid + foo combination is unique within choice I would recommend not to add more values into the subquery. Otherwise the overall approach needs to be changed.

Upvotes: 2

Ami
Ami

Reputation: 1254

You can accomplish this very efficiently with a join like this:

SELECT *
FROM node
LEFT JOIN choice c1
  ON c1.nid = node.nid
LEFT JOIN choice c2
  ON c2.nid = node.nid
  AND c2.foo > c1.foo
WHERE c2.foo IS NULL

The one draw back to this method is that if you have records with duplicate foo, you will get duplicate records.

Upvotes: 1

Related Questions