James B James B - 4 months ago 7
SQL Question

Select lowest value and return first row only

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 RANK)
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'
ORDER BY RESULT;

Answer

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 ROW_NUMBER().