January 31, 2008 at 8:00 am
Hi..here i am explaining the problem...
In my DB, I have table called 'Marks'.which contains Classid,Studentid,totalmarks.
In my table i am storing the data related to all classes. for example in a school there are 10 classes and each class is having 50 students with roll numbers 1 to 50. (for each class the roll numbers are same from 1 to 50).
Actual problem is i need to find out the student who scores highest marks in his class(i need to find out the best student in each class).
January 31, 2008 at 10:05 am
Please in the future post schema, sample data and expected results. See - http://www.sqlservercentral.com/articles/Best+Practices/61537/
First question - is this homework?
You need a group by on classID and a max on the mark. That will give you the highest mark in the class. Use that as a subquery and join it back to the table on both classID and max mark, and you'll get the studentID that has the highest mark per class.
Does that help?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 31, 2008 at 10:35 am
In addition to Gila's suggestions, since you're in 2005 - you might care to read up on the RANKING functions available. Books online have some good examples.
Good luck on the homework assignment.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 31, 2008 at 11:14 am
Thank you very much for the help...
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply