Highest grade - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Highest grade

Author

Topic

derok
Starting Member

14 Posts

Posted - 2008-04-25 : 14:56:33

Hi,
I have this table in sql server:
It record the info of the grades of students.

Table: Grades
Columns: GradeId, Grade, Id, SubjectId.

i'm doing this to select the highest grade:


select max(grade)
from grades

it works fine, it displays the highest grade,
but i want that, it also displays the id of the student

what can i do?

thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-25 : 14:59:16

I'm sure this is a homework question, but since you showed us what you tried, I'll bite.

SELECT id, MAX(grade) AS HighestGrade
FROM grades
GROUP BY id

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

derok
Starting Member

14 Posts

Posted - 2008-04-25 : 15:09:51

quote:
Originally posted by tkizer

I'm sure this is a homework question, but since you showed us what you tried, I'll bite.

SELECT id, MAX(grade) AS HighestGrade
FROM grades
GROUP BY id

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

thanks, but

this code:
select max(grade)
from grades

shows this:
87.0

and this:

SELECT id, MAX(grade) AS HighestGrade
FROM grades
GROUP BY id

shows this:

id--------HighestGrade
21--------54
32--------87

what i want is that only the highest grade which is 87 and the corresponen id "32", gets printed on screen.

what can i do?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-25 : 15:11:44

You can do this then:

SELECT id, grade
FROM grades
WHERE grade = (SELECT MAX(grade) FROM grades)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

derok
Starting Member

14 Posts

Posted - 2008-04-25 : 15:25:53

quote:
Originally posted by tkizer

You can do this then:

SELECT id, grade
FROM grades
WHERE grade = (SELECT MAX(grade) FROM grades)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

thanks
you're incredible!!

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-04-26 : 02:15:30

or:

select top 1 id, grade
from grades
order by grade desc

elsasoft.org

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-28 : 06:12:20

or:

select top 1 with ties id, grade
from grades
order by grade desc

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

derok
Starting Member

14 Posts

Posted - 2008-05-04 : 14:41:30

thanks guys!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources