Help Display The Minimun value

  • Thread starter Thread starter nidhi9211
  • Start date Start date
N

nidhi9211

Hi -

I have fourcolumns subject-id, stud-id, stud-name and score in an
Access table. For every subject id there are multiple records for
various students' scores. 1. I need to display only unique records
that display every subject id once with the student information who
scored the minimum marks in that subject.2. If possible then display
second minimun and third minimum in other columns.I have used distinct
with subject-id and the min(score) as a column value for score with
order by min(score) but it arranges all the records in asec scores and
hence does not fulfill the purpose of display one row for each subject
id.
Thanks in anticipation.
 
Try this --
SELECT Q.[subject-id], Q.[stud-id], Q.[stud-name], Q.score, (SELECT COUNT(*)
FROM [nidhi] Q1
WHERE Q1.[subject-id] = Q.[subject-id]
AND Q1.[score] < Q.[score])+1 AS Rank
FROM nidhi AS Q
WHERE ((((SELECT COUNT(*) FROM [nidhi] Q1
WHERE Q1.[subject-id] = Q.[subject-id]
AND Q1.[score] < Q.[score])+1)<=3))
ORDER BY Q.[subject-id], Q.score;
 
Hi -
Thanks for your reply. It sure is helpful to see the ranks in a
different column.
The output that i am trying to get however is to get a single row for
each subject and have student info with rank 1 and 2 in different
columns.
Again, I appreciate the help very much.
 
I named the other query 'Low3_Subj_Scores' and this one pulls from it.

SELECT Low3_Subj_Scores.[subject-id], Low3_Subj_Scores.Rank AS Lowest,
Low3_Subj_Scores.[stud-id] AS [Lowest Student], Low3_Subj_Scores.[stud-name]
AS [Lowest Name], Low3_Subj_Scores.score AS [Loest Score],
Low3_Subj_Scores_1.Rank AS Middle, Low3_Subj_Scores_1.[stud-id] AS [Middle
Student], Low3_Subj_Scores_1.[stud-name] AS [Middle Name],
Low3_Subj_Scores_1.score AS [Middle Score], Low3_Subj_Scores_2.Rank AS [3rd
Lowest], Low3_Subj_Scores_2.[stud-id] AS [3rd Lowest Student],
Low3_Subj_Scores_2.[stud-name] AS [3rd Lowest Name], Low3_Subj_Scores_2.score
AS [3rd Lowest Score]
FROM (Low3_Subj_Scores INNER JOIN Low3_Subj_Scores AS Low3_Subj_Scores_1 ON
Low3_Subj_Scores.[subject-id] = Low3_Subj_Scores_1.[subject-id]) INNER JOIN
Low3_Subj_Scores AS Low3_Subj_Scores_2 ON Low3_Subj_Scores.[subject-id] =
Low3_Subj_Scores_2.[subject-id]
WHERE (((Low3_Subj_Scores.Rank)=1) AND ((Low3_Subj_Scores_1.Rank)=2) AND
((Low3_Subj_Scores_2.Rank)=3));
 
Back
Top