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!

I dont have oracle in front of me, but did you try `SELECT max(stdid) INTO v_stdid` — Nilesh, Aug 04 '13 at 02:44
Thanks Nilesh - I tried this but still get the wrong value (9 instead of 32). — teppuus, Aug 04 '13 at 03:19
Hi Nilesh - the max works now. :) I was trying to use max on a varchar value, so that was messing me up. I used this and it worked: select max(to_number(stdid)) into v_stdid from student; Thanks again! — teppuus, Aug 04 '13 at 03:25
possible duplicate of [ORDER BY function not working in Oracle](http://stackoverflow.com/questions/13823629/order-by-function-not-working-in-oracle) — APC, Aug 04 '13 at 08:13

score 0 · Answer 1 · edited May 23 '17 at 12:28

0

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

edited May 23 '17 at 12:28

Community

1
1

answered Aug 04 '13 at 02:51

mattpic

1,277
9
13

Thanks Mattpic - Can't use TOP keyword unfortunately. It seems that is for SQL Select. I found an article that said the Oracle equivalent to TOP is using ROWNUM, but like the link you gave pointed out, ROWNUM creates its own problems. – teppuus Aug 04 '13 at 03:22
Glad I could help, didn't realize you were using Oracle. Simple fix, but if you put the ROWNUM inside the parentheses does that fix the problem? – mattpic Aug 04 '13 at 03:27
Adding ROWNUM to the sub-query won't work either, because it is applied before the sort and so the sub-query won't retune the highest value. – APC Aug 04 '13 at 08:09

score 0 · Answer 2 · answered Aug 04 '13 at 15:03

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;

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

2 Answers2