H

[nebakanezer]

Hi, im trying to find the module with the highest exam mark. Anyone spot the error.

SQL> select mname, X.cmark, Y.cmark from result X,result Y, module
2 where result.mcode = module.mcode
3 and X.cmark > Y.cmark;

where result.mcode = module.mcode
*
ERROR at line 2:
ORA-00904: "RESULT"."MCODE": invalid identifier

thanx.

 

[Katy44]

I think you need to use your aliases in the where line - is it result X or Y that equals module.mcode?

 

[nebakanezer]

thanx for the reply. I tried this but still no luck.

SQL> select mname, X.cmark, Y.cmark from result X,result Y, module
2 where result X.mcode and result Y.mocode = module.mcode
3 and X.cmark > Y.cmark;


where result X.mcode and result Y.mocode = module.mcode
*
ERROR at line 2:
ORA-00920: invalid relational operator

sorry about the heading btw forgot to put it in.

 

[Katy44]

If this was the case you need:

select mname, X.cmark, Y.cmark from result X,result Y, module
where result X.mcode = module.mcode
and result Y.mocode = module.mcode
and X.cmark > Y.cmark;

But is this really what you want to do?

 

[nebakanezer]

Still does not work.
As i said im trying to find the highest exam mark. My logic was to do a comparrison with X Y cmark from the results table. But i also need to join the result and module tables in order to display the module name. Thats it really, dont know if i am approaching it in the correct way?

thanx

 

[Katy44]

Are you trying to find the highest mark then which module led to it?

So:
select max(cmark)
from result

gives you the highest mark, and then you also want to know the module name?

 

[nebakanezer]

Yes with the module name as well, but i tried:

select max(emark), mname from result, module
where result.mcode = module.mcode;

and it says not a single-group function

 

[Katy44]

A way to sort of do this:

select mname, max(emark)
from result, module
where result.mcode = module.mcode
group by mname
order by max(emark) desc

would give you all modules but in the right order

but there must be a way to do this properly and it's bugging me!

 

[Katy44]

You could make this a subquery and select the max of (max(emark)) (and the corresponding module name) - but there must be a better way!

 

[nebakanezer]

Ok thats got it thanx for you assistance anyway.