3

I have this SQL:

SELECT itemId, parentId, value FROM item ORDER BY value DESC

which correctly returns:

+--------+------------------+
| itemId | parentId | value |
+--------+------------------+
|      1 |        5 |   500 |
|      4 |        1 |   500 |
|      2 |        5 |    10 |
|      5 |        1 |    10 |
|      3 |        5 |     0 |
|      6 |        1 |     0 |
+--------+----------+-------+

I tried adding "GROUP BY parentId", but this seems to pick two random items, ignoring the ORDER BY clause:

+--------+------------------+
| itemId | parentId | value |
+--------+------------------+
|      2 |        5 |    10 |
|      6 |        1 |     0 |
+--------+----------+-------+

What SQL should I use to return only the item with the highest value for each parentId?:

+--------+------------------+
| itemId | parentId | value |
+--------+------------------+
|      1 |        5 |   500 |
|      4 |        1 |   500 |
+--------+----------+-------+
Sarah Northway
  • 1,029
  • 1
  • 14
  • 24
  • select itemId, parentId, max(value) as val from item group by parentId order by val desc – Reddy Sep 23 '12 at 17:10
  • This would give me results like [itemId=2, parentId=5, value=10, val=500]. I need to return the correct itemId associated with the highest value, not just the highest value itself. – Sarah Northway Sep 23 '12 at 17:48

1 Answers1

1

For each itemId, only select it if its value is the MAX of its parentId:

SELECT itemId, parentId, value
FROM item i
WHERE value = (SELECT MAX(value) 
               FROM item t 
               WHERE t.parentId = i.parentId)

Which returns:

| ITEMID | PARENTID | VALUE |
-----------------------------
|      1 |        5 |   500 |
|      4 |        1 |   500 |

DEMO.

João Silva
  • 89,303
  • 29
  • 152
  • 158
  • Yes this works! I had to add GROUP BY for the case where two itemIds have the same parentId and value (I only want one), and ORDER BY to maintain that the biggest value come first no matter what parentId. – Sarah Northway Sep 23 '12 at 17:38