3rd Max for every student

[ionbadea]

Hi,

I have a simple table with students marks and I want to extract the 3rd highest mark for every one of them.
Input table

ID	Name	Mark
1	Student1	5
2	Student1	4
3	Student1	3
4	Student1	1
5	Student2	8
6	Student2	9
7	Student2	10
8	Student2	7

The answer should be Student1=3 Student2=8. Could you tell me how to do it in MS Access 2013?

[ItsMe]

Maybe you could use a SELECT TOP statement.
http://www.w3schools.com/sql/sql_top.asp

[InsuranceGuy]

Access doesn't do rankings well in queries. It can be done as outlined here though:

https://support.microsoft.com/en-us/kb/120608

Cheers,

Jeff

[orange]

When you are looking for the 3rd highest, you can consider the following scenario:

Get the highest number, where the number is not in the Top 2 highest numbers.

That is ignore/remove the TOP 2, then the topmost(Top 1 remaining) is the 3rd highest of the original.

SELECT sname
      ,smark
FROM studentmarks A
WHERE a.smark IN (
        SELECT TOP 1 smark
        FROM studentmarks ZZ
        WHERE zz.sname = a.sname
            AND smark NOT IN (
                SELECT TOP 2 smark
                FROM studentmarks ww
                WHERE ww.sname = a.sname
                ORDER BY smark DESC
                )
        )
GROUP BY sname
    ,smark
ORDER BY smark DESC;

I used your data to create table StudentMarks with fields Id, SName and SMark.
Result based on the data provided:

sname	smark
Student2	8
Student1	3

[InsuranceGuy]

@orange, I like this!!

[orange]

Thanks InsuranceGuy. There may be other ways.
Also, based on your previous post - I don't consider this a ranking query.
It seems to me to be more - selecting specific records from groups.

[ionbadea]

Thanks Orange for your quick answer. When I writed your code into my DB I am asked to insert Sname and Smark. I presume I did not addapted something. I am an Access newbie. In your code syntax "FROM studentmarks A" what does "A" means?

[orange]

A few things:

Name is a reserved word in Access. see Reserved words
I took your data and made a table.
The table name is StudentMarks.
The fields are ID, SMark and SName.
"A" is an alias, as are ZZ and ww. see Using Aliases

Good luck.