We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Access - need to get the max value from 8 different columns in a row...

Medium Priority
357 Views
Last Modified: 2012-05-06
I need to get the max value from 8 different columns in a row...

i.e.

Name   Score1 Score2  Score3  Score4  Score5 Score6 Score7 Score8
Joe             80     81         82            83        89         98        99         76
Bob            69     71          83            85       86          70        72        71
Comment
Watch Question

Top Expert 2009

Commented:
SELECT   [Name]
                  , Max(Score1)
                  , Max(Score2)
                  , Max(Score3)
                  , Max(Score4)
                  , Max(Score5)
                  , Max(Score6)
                  , Max(Score7)
                  , Max(Score8)
FROM      table1
GROUP BY [Name]
Database Developer
CERTIFIED EXPERT
Commented:
Is that your table schema?  8 columns for maintaining 8 different score values?
Is it your intent to determine the maximum score across all 8 per name?
The intense repeating field causes you the problem of trivially determining the maximum across all scores.
If you normalised your structure into something more like

tblPersons
PersonID
PersonName

tblScores
PersonID
ScorePos
Score

A single, simple, standard statement would give you your result
SELECT PersonName, Max(Score)
FROM tblScores S INNER JOIN tblPersons P ON S.PersonID = P.PersonID
GROUP BY PersonName

As it is - you can either use some unrefined function type comparison to determine the maximum across rows or UNION your way to quasi-normalisation... :-s

SELECT X.Name, Max(X.Score)
FROM
(SELECT Name, Score1 As Score FROM TableName
UNION ALL
SELECT Name, Score2 FROM TableName
UNION ALL
SELECT Name, Score3 FROM TableName
UNION ALL
SELECT Name, Score4 FROM TableName
UNION ALL
SELECT Name, Score5 FROM TableName
UNION ALL
SELECT Name, Score6 FROM TableName
UNION ALL
SELECT Name, Score7 FROM TableName
UNION ALL
SELECT Name, Score8 FROM TableName) X
GROUP BY X.Name

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
LPurvis:
The data set of the different scores is a result of a very complex query to capture the grades score of a student for each class across mutliple grading periods.
The scores are part of a longitudinal study of grades - each grade (score) is a grade for  a different grading period for the class.  Each grading period has its own attribute in the database.  We need the max grade of them all...
 I omitted the class and course data for ease of understanding, as it is inconsequential.
 So really what you have is each student will show up for each class - each class is a row.
Name   Class Score1 Score2  Score3  Score4  Score5 Score6 Score7 Score8
Joe           Math          80     81         82            83        89         98        99         76
Joe           Hist        100   99        78            98         77         45         50        78

Bob          Eng        69     71          83            85       86          70        72        71
Bob           Science  89      89           83           65        68          67        90        71  

Author

Commented:
LPURVIS - I was able to use your example to get the results I needed for MAx across different columns (attributes) - see below

SELECT x.STID, x.STLNAM, x.STFNAM, x.srcrse, x.srsect, x.smpdfm, Max(x.score) AS MaxOfscore, x.teacher
FROM (SELECT longitudinal.STID, longitudinal.STLNAM, longitudinal.STFNAM,longitudinal.srcrse, longitudinal.srsect, longitudinal.smpdfm, longitudinal.[1st Q INT] as score, longitudinal.teacher
FROM longitudinal
union all
SELECT longitudinal.STID, longitudinal.STLNAM, longitudinal.STFNAM, longitudinal.srcrse, longitudinal.srsect, longitudinal.smpdfm, longitudinal.[1st Q],  longitudinal.teacher
FROM longitudinal
union all
SELECT longitudinal.STID, longitudinal.STLNAM, longitudinal.STFNAM,longitudinal.srcrse, longitudinal.srsect, longitudinal.smpdfm, longitudinal.[2nd Q INT] as score, longitudinal.teacher
FROM longitudinal
union all
SELECT longitudinal.STID, longitudinal.STLNAM, longitudinal.STFNAM, longitudinal.srcrse, longitudinal.srsect, longitudinal.smpdfm, longitudinal.[2nd Q],  longitudinal.teacher
FROM longitudinal
union all
SELECT longitudinal.STID, longitudinal.STLNAM, longitudinal.STFNAM,longitudinal.srcrse, longitudinal.srsect, longitudinal.smpdfm, longitudinal.[3rd Q INT] as score, longitudinal.teacher
FROM longitudinal
union all
SELECT longitudinal.STID, longitudinal.STLNAM, longitudinal.STFNAM, longitudinal.srcrse, longitudinal.srsect, longitudinal.smpdfm, longitudinal.[3rd Q],  longitudinal.teacher
FROM longitudinal
union all
SELECT longitudinal.STID, longitudinal.STLNAM, longitudinal.STFNAM, longitudinal.srcrse, longitudinal.srsect, longitudinal.smpdfm, longitudinal.[4th Q INT],  longitudinal.teacher
FROM longitudinal
union all
SELECT longitudinal.STID, longitudinal.STLNAM, longitudinal.STFNAM,longitudinal.srcrse, longitudinal.srsect, longitudinal.smpdfm, longitudinal.[4th Q],  longitudinal.teacher
FROM longitudinal) AS x
GROUP BY  x.STLNAM, x.STFNAM, x.smpdfm, x.srcrse, x.srsect, x.STID, x.teacher;
Leigh PurvisDatabase Developer
CERTIFIED EXPERT

Commented:
Understood and glad you're up and running.
Are you saying that the repeating value for Score is a result of  pivoted data, or that the repeating Score fields are somehow required because of the Student and class data stored?
Again, very simplified, but:

Students
StudentID
PersonName

Classes
ClassID
ClassName
ClassSubjectID
StudentClasses
StudentClassID
StudentID
ClassID

Scores
StudentClassID
ScorePos
Score
Cheers.

Author

Commented:
L Purvis -
Are you saying that the repeating value for Score is a result of  pivoted data, or that the repeating Score fields are somehow required because of the Student and class data stored?
The repeating scores are coming from a vendor database (and table) that is not very normalized.
  • In additon, the scores are from various quaters, semesters, and interim grades with thieer own attributes in the same table.

 
Leigh PurvisDatabase Developer
CERTIFIED EXPERT

Commented:
Well I was wanting to make sure that this wasn't the standard schema - as you point out, it wouldn't be at all normalised and is the cause for the need of the rather convoluted query workaround.
It's possible that querying whatever data that created this pivoted resultset would yield more efficient results.
Cheers.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.