Can anyone tell me how to fix this? My order by is by coursenum (for example, CS 20, CS 25, CS 100 are all course numbers) ascending. It's counting the first digit instead of the whole number though:
-----------------------------------------
Course Grade
-----------------------------------------
CS 120 Intro to Java Programming A
CS 140 Structured Analysis A
CS 20 Intro to Computers F
CS 25 Intro to Programming B
The problem here is that your database is doing a string sort, rather than a numeric sort. A computer won't be able to look at that data and infer a semantic sorting order. A string sort is done by taking two strings, and comparing each character until one character comes before another. So, in your example, each of those courses begins with "CS ", but then the 4th character is a numeric character (which is not the same as a numeric type!). the "1"s sort as lower than the "2"s, and then in the "1"s, the "2" sorts lower than the "4", and so on.
To solve this, you'd either need to zero pad the course number ("CS 020") or you'd need a separate numeric column with the course number to sort on.
Strip out the non-digital characters and use to_number(),
with t as (
select 'CS 120 Intro to Java Programming' course from dual
union
select 'CS 140 Structured Analysis' course from dual
union
select 'CS 20 Intro to Computers' course from dual
union
select 'CS 25 Intro to Programming' course from dual
)
select
course, regexp_replace(course, '[^0-9]', null) n
from
t
order by
to_number(regexp_replace(course, '[^0-9]', null))
;
