CODEWITHSUNDEEP
sqloracle-database
James B
James B

Reputation: 231

Select lowest number from column and show other columns

I need to find the lowest number in my RESULT column which I know can be done with MIN but I am not sure how to do this as well as select my other columns. My code displays all the results for all members but I just want it to show the best result (lowest number) along with the other details for that member.

Right now I have multiple entries that look like this:

EVENT_ID    EVENT_TYPE  EVENT_NAME  MEMBER_ID   MEMBER_FIRSTNAME    MEMBER_LASTNAME RESULT
event1      track       100M        0001        John                Smith           11.3

I just need to select the row with the lowest result and show that one only. Here is my code so far:

SELECT E.EVENT_ID, E.EVENT_TYPE, E.EVENT_NAME, R.MEMBER_ID, M.MEMBER_FIRSTNAME, M.MEMBER_LASTNAME, R.RESULT
FROM EVENT E, MEMBER M, RANK R
WHERE E.EVENT_ID = R.EVENT_ID
AND R.MEMBER_ID = M.MEMBER_ID;

SGEDDES - I tried following your last method and couldn't get it to work:

SELECT E.EVENT_ID, E.EVENT_TYPE, E.EVENT_NAME, R.MEMBER_ID, M.MEMBER_FIRSTNAME, M.MEMBER_LASTNAME, R.RESULT
FROM (EVENT E, MEMBER M, RANK R
WHERE E.EVENT_ID = R.EVENT_ID
AND R.MEMBER_ID = M.MEMBER_ID;
ORDER BY RESULT)
WHERE ROWNUM = 1;

Upvotes: 2

Views: 259

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269793

In the most recent versions of Oracle, you can use fetch first 1 row only:

SELECT E.EVENT_ID, E.EVENT_TYPE, E.EVENT_NAME, R.MEMBER_ID,
       M.MEMBER_FIRSTNAME, M.MEMBER_LASTNAME, R.RESULT
FROM EVENT E JOIN
     RANK R
     ON E.EVENT_ID = R.EVENT_ID JOIN
     MEMBER M
     ON R.MEMBER_ID = M.MEMBER_ID
ORDER BY R.RESULT
FETCH FIRST 1 ROW ONLY;

If you have a less recent version of Oracle, then one of sgeddes's solutions is fine.

Upvotes: 0

sgeddes
sgeddes

Reputation: 62841

Here's one option using row_number (also please note the join syntax -- in general you shouldn't use commas in the from clause):

SELECT * 
FROM (
    SELECT E.EVENT_ID, E.EVENT_TYPE, E.EVENT_NAME, 
         R.MEMBER_ID, M.MEMBER_FIRSTNAME, M.MEMBER_LASTNAME, R.RESULT,
         row_number() over (order by R.RESULT) rn
    FROM EVENT E JOIN RANK R ON E.EVENT_ID = R.EVENT_ID
         JOIN MEMBER M ON R.MEMBER_ID = M.MEMBER_ID
) t
WHERE rn = 1

If you need to group the results by a specific field(s), you an use partition by. For example:

row_number() over (partition by e.event_id order by result) rn

And here's another option using rownum:

SELECT *
  FROM (<<YOUR QUERY HERE>> ORDER BY R.Result )
 WHERE ROWNUM = 1;

Upvotes: 1

Related Questions