Let say I want to select information as state in following SQL:

<SQL>
SELECT code_orgstructure.ext_desc,
(select users.givenname from users where users.userid =
transcript_module.userid),
(select users.familyname from users where users.userid =
transcript_module.userid),
(select learningObject.learningid from learningObject where
learningObject.learningid = transcript_module.learningid),
(select learningObject.title from learningObject where
learningObject.learningid = transcript_module.learningid),
avg(CONVERT(float, AICC_core.highestscore))
FROM transcript transcript_module, code_orgstructure code_orgstructure,
AICC_core AICC_core, users users
WHERE users.userid = transcript_module.userid AND AICC_core.session_id
= transcript_module.contentSessionID AND users.leaf_orgid =
code_orgstructure.orgid GROUP BY code_orgstructure.ext_desc
</SQL>

Is it possible to get the same information without subquery? At first I
thought it can be done with

<SQL>
SELECT code_orgstructure.ext_desc, max(CONVERT(float,
AICC_core.highestscore)), users.familyname, users.givenname, learningObject.learningid, learningObject.title FROM transcript transcript_module, code_orgstructure code_orgstructure, learningObject
learningObject, AICC_core AICC_core, users users
WHERE users.userid = transcript_module.userid AND learningObject.learningid = transcript_module.learningid AND transcript_module.userid = users.userid
AND transcript_module.learningid = learningObject.learningid AND
transcript_module.contentSessionID = AICC_core.session_id AND
users.leaf_orgid = code_orgstructure.orgid GROUP BY
code_orgstructure.ext_desc
</SQL>

but of course it fail....

You need to add all the non-aggregate columns to the GROUP BY clause:

GROUP BY SELECT code_orgstructure.ext_desc, users.familyname, users.givenname, learningObject.learningid, learningObject.title;

Thx for reply, however, in my example, what I like to do is select the highest marks among the whole organization, and display the organization name, highest mark, user name and related course name of that highest mark record. How can I do?

If I include the user name, and course name in group by, then it just show the highest score of that student in that course, which is not what I want... Or I should write something like

select orgname, max(username), max(course_name), max(score) ..... group by orgname

??

I'd try:to see what that does.

-PatP