combining a sort, select into, and where statement to find a max value in oracle sql

Question

I am trying to do a select into statement to get a max value from a column. This is what I have:

   SELECT stdid INTO v_stdid 
   FROM (SELECT stdid FROM STUDENT ORDER BY stdid DESC)
   WHERE ROWNUM = 1;

I don't get the correct value though. It should be 32, but 9 is returned instead (I have a dbms_output_line(v_stdid) to check for the value. When I change the order by to ASCENDING my value changes, so I know something is happening. But why can't I get the correct max value? I feel the sort must not be completing properly.

Thank you!

Answer 1

You can do it this way:

SELECT max(stdid) INTO v_stdid 
FROM STUDENT;

But, I believe your problem is that stdid is being stored as a character rather than as a number. So, convert it to an integer:

select max(cast(stdid as int)) into v_stdid
from student;

This would also work with your original formulation:

SELECT stdid INTO v_stdid 
FROM (SELECT stdid FROM STUDENT ORDER BY cast(stdid as int) DESC)
WHERE ROWNUM = 1;

Answer 2

It seems that the TOP keyword might be helpful for you.

SELECT stdid INTO v_stdid
FROM (SELECT TOP 1 FROM STUDENT ORDER BY stdid DESC);

I believe this eliminates the problem you were having by returning the stdid of the top 1 result from the second select into the v_stdid, without having to worry about some of the intricacies that NUMROW has, as evidenced here:

Selecting the second row of a table using rownum