SQL query to select highest test score

40 pts.
Tags:
Distinct Records
MAX
SQL
SQL Query
SQL tables
I am writing a sql to bring someone's highest test score into a table. The sql is bringing in the highest test score possible, rather than the person's test score. I assume MAX is making that happen. If I take MAX out, it returns the single row subquery returns more than one row error. What can I do to have it return the actual test score of the person? Thank you for your help. update table set act_cmpswrt_score = (Select MAX(sortest.sortest_test_score) from sortest where sortest.sortest_tesc_code = 'CP1') where exists (select sortest.sortest_test_score from sortest where sortest.sortest_pidm = table.act_pidm);

Answer Wiki

Thanks. We'll let you know when a new response is added.

One solution could be adding a condition to the subquery to return the max score of the specific person.

Something like this (I added an alias for TABLE, and did some other naming changes):

<pre>update table t
set act_cmpswrt_score =
(Select MAX(sortest_test_score) from sortest where sortest_tesc_code = ‘CP1′ and sortest_pidm = t.act_pidm)
where exists
(select sortest_test_score
from sortest
where sortest_pidm = t.act_pidm); </pre>

I don’t know if this will work on all databases, and I also do not know what database are you using, so please, let us know if it worked.

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Cmyers
    I appreciate your suggestion. When I tried it, it returned an error of "invalid relational operator" at the "where exists" line. The database is Banner through Oracle. If you have any further suggestions, they will be appreciated. Thanks again for your time.
    40 pointsBadges:
    report
  • carlosdl
    Did you copy and paste ? I don't think the problem is really the 'where exists' part, since you had it on your query, and it was giving no errors. The only thing I added is a new condition to the subquery that gets the max(sortest_test_score), but that part is not visible above, so I was wondering if you did not miss that part.
    68,650 pointsBadges:
    report
  • Cmyers
    I didn't enter the new sql with your changes. Here it is: update jwcc_act set act_cmpswrt_score = (Select MAX(sortest_test_score)from sortest where sortest_tesc_code = 'CP1' and so where exists (select sortest_test_score from sortest where sortest_pidm = jwcc_act.act_pidm); [End of file] It does seem that sometimes the errors are misleading to the true problem, so I understand that it may not be the "where exists" line, it's just where the error pointed to. I thought your original response had the "and so" to line 4 although I don't see it now.
    40 pointsBadges:
    report
  • carlosdl
    The statement is not completely visible above. update table t set act_cmpswrt_score = (Select MAX(sortest_test_score) from sortest where sortest_tesc_code = 'CP1' and sortest_pidm = t.act_pidm) where exists (select sortest_test_score from sortest where sortest_pidm = t.act_pidm);
    68,650 pointsBadges:
    report
  • Cmyers
    Yes, that works. Most excellent. Thank you for your help.
    40 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following