I am having difficulty returning the first row only from the following query. I am trying to return the lowest value in the RESULT column for a particular EVENT which works fine but it also returns other rows, I just want the one. What is the best way to achieve this?
SELECT E.EVENT_ID, E.EVENT_TYPE, E.EVENT_NAME, M.MEMBER_ID, M.MEMBER_FIRSTNAME, M.MEMBER_LASTNAME, (SELECT MIN(RESULT)
FROM EVENT E JOIN
ON E.EVENT_ID = R.EVENT_ID JOIN
ON M.MEMBER_ID = R.MEMBER_ID
WHERE E.EVENT_ID = 'EVENT001'
ORDER BY RESULT;
The subquery in the solution below is copied and pasted from your original post, I didn't test it (obviously since I didn't have your tables), EXCEPT for the extra field: ROW_NUMBER() OVER .... instead of the MIN(RESULT...)
This assigns a row in each "partition" using the ordering indicated (notice DESC), and in the outer query you select the rows where this row_number equals 1.
SELECT EVENT_ID, EVENT_TYPE, EVENT_NAME, MEMBER_ID, MEMBER_FIRSTNAME, MEMBER_LASTNAME, RESULT FROM ( SELECT E.EVENT_ID, E.EVENT_TYPE, E.EVENT_NAME, M.MEMBER_ID, M.MEMBER_FIRSTNAME, M.MEMBER_LASTNAME, R.RESULT, row_number() over (partition by e.event_id order by r.result) as rn FROM EVENT E JOIN RANK R ON E.EVENT_ID = R.EVENT_ID JOIN MEMBER M ON M.MEMBER_ID = R.MEMBER_ID WHERE E.EVENT_ID = 'EVENT001' ) WHERE RN = 1;
This assumes you want to return only one row, even if there is a tie for the lowest score. If there are ties, which row will be selected is undetermined (at least with this solution; you could add more clauses in ORDER BY). If in the case of ties you want ALL tied members to be listed, then you can use
DENSE_RANK() instead of