Count and Group By

[orne30712]

I have a dataset that looks like this:

CREATE TABLE Scores ([ID] IDENTITY(1,1), StudentID INT, SubjectID INT, Score
FLOAT)

I want to get the lowest score and the subject for each student. So the
results will be

Student 1 MATH 50
Student 2 ENG 60
Student 3 MATH 55
Student 4 MATH 57

This query only gives me the StudentID and Max Score but not the SubjectID:

SELECT StudentID, MAX(Score)
FROM Scores
GROUP BY StudentID

If I add the SubjectID, it gives me every subject for every student:

SELECT StudentID, SubjectID, MAX(Score)
FROM Scores
GROUP BY StudentID, SubjectID

Any idea?
TIA

 

[Michel Walsh]

You can use one of the four techniques presented at
http://www.mvps.org/access/queries/qry0020.htm



The easiest one is probably to do it in two steps. The first query get the
MIN (or MAX) per student (your first query). Then, make a second query,
bring the table and the query you just made, link the two through their
field StudentID and YourTable.Score-YourQuery.MinOfScore.


Sure, if Student1 has two scores of 50, and both minimum, they will both be
selected.



Hoping it may help,
Vanderghast, Access MVP

 

[KARL DEWEY]

Try this --
SELECT T.StudentID, T.SubjectID, T.Score
FROM Scores AS T
WHERE ((((SELECT COUNT(*)
FROM [Scores] T1
WHERE T1.StudentID = T.StudentID
AND T1.Score <= T.Score))=1))
ORDER BY T.StudentID, T.Score;

 

[KARL DEWEY]

Michel Walsh post reminded me that identical scores at the low end would not
be selected as they would both be ranked as 2nd. Below brings in 1st and 2nd
scores.

SELECT T.StudentID, T.SubjectID, T.Score
FROM Scores AS T
WHERE ((((SELECT COUNT(*)
FROM [Scores] T1
WHERE T1.StudentID = T.StudentID
AND T1.Score <= T.Score))<=2))
ORDER BY T.StudentID, T.Score;

--
KARL DEWEY
Build a little - Test a little

Try this --
SELECT T.StudentID, T.SubjectID, T.Score
FROM Scores AS T
WHERE ((((SELECT COUNT(*)
FROM [Scores] T1
WHERE T1.StudentID = T.StudentID
AND T1.Score <= T.Score))=1))
ORDER BY T.StudentID, T.Score;

--
KARL DEWEY
Build a little - Test a little

I have a dataset that looks like this:

CREATE TABLE Scores ([ID] IDENTITY(1,1), StudentID INT, SubjectID INT, Score
FLOAT)

I want to get the lowest score and the subject for each student. So the
results will be

Student 1 MATH 50
Student 2 ENG 60
Student 3 MATH 55
Student 4 MATH 57

This query only gives me the StudentID and Max Score but not the SubjectID:

SELECT StudentID, MAX(Score)
FROM Scores
GROUP BY StudentID

If I add the SubjectID, it gives me every subject for every student:

SELECT StudentID, SubjectID, MAX(Score)
FROM Scores
GROUP BY StudentID, SubjectID

Any idea?
TIA