Lets say I have a table with: key, groupID, valueOfInterest and
sortingValue, all ints.
I want the valueOfInterest where sortingValue is as large as possible,
but limited by 10 (or some value i calculate...)
I was pretty sure I managed this earlier by:
SELECT key, groupID, valueOfInterest, sortingValue, MAX(sortingValue)
FROM table WHERE sortingValue<10 GROUP BY groupID;
But this seems to return some random row and the max-values. Instead I
have come up with:
SELECT key, groupID, valueOfInterest, sortingValue FROM table WHERE
sortingValue IN (SELECT MAX(sortingValue) FROM table WHERE
sortingValue sortingValue < 10 GROUP BY groupID);
Is there a ...