[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2009-02-12
7
Medium Priority
?
347 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
0
Comment
Question by:tl121000
  • 3
  • 3
7 Comments
 
LVL 27

Expert Comment

by:nmarun
ID: 23629196
SELECT   [Name]
                  , Max(Score1)
                  , Max(Score2)
                  , Max(Score3)
                  , Max(Score4)
                  , Max(Score5)
                  , Max(Score6)
                  , Max(Score7)
                  , Max(Score8)
FROM      table1
GROUP BY [Name]
0
 
LVL 44

Accepted Solution

by:
Leigh Purvis earned 1000 total points
ID: 23629212
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
0
 
LVL 9

Author Comment

by:tl121000
ID: 23629435
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  
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 9

Author Closing Comment

by:tl121000
ID: 31546433
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;
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 23631450
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.
0
 
LVL 9

Author Comment

by:tl121000
ID: 23665504
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.

 
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 23666599
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.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question