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.
| Author |
Topic |
|
anubhavmax
Starting Member
10 Posts |
 Posted - 2010-09-20 : 03:12:13
|
| I need a query that can display top 5 students of each subject(physic, chemistry, maths , computer) securing the maximum marks? How do I design the tables for that and write a single query to do it?The information stored are Name , subject, marks. The query has to display top 5 marks scored by students in each subject. So in all 20 results has to be displayed... 5 for each subject. How do I design the table and write the query for this? |
|
|
rohitvishwakarma
Posting Yak Master
232 Posts |
Posted - 2010-09-20 : 03:19:39
|
| Craete a table as student_master having student related data such as roll_bo & name(mark roll_no as primary_key).Create a seond table student_marks having columns: roll_no(foreign_key),subject & marks. |
 |
|
|
anubhavmax
Starting Member
10 Posts |
Posted - 2010-09-20 : 03:22:09
|
| Then what about the query to select top 5 from each subject. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-09-20 : 04:08:35
|
| If you're doing a basic database course, look in your coursework (or ask a tutor) about the TOP keyword and also about ORDER BY.--Gail ShawSQL Server MVP |
|
|
|
rohitvishwakarma
Posting Yak Master
232 Posts |
Posted - 2010-09-20 : 04:46:53
|
| Here is your query:SELECT A.*,B.name FROM (select subject, marks,roll_no, RANK() OVER (PARTITION BY subject ORDER BY marks DESC) AS rank FROM student_marks)A INNER JOIN student_master B ON A.roll_no = B.roll_noWHERE A.rank IN (5,4,3,2,1) |
|
|
|
anubhavmax
Starting Member
10 Posts |
Posted - 2010-09-20 : 07:49:55
|
| Thanks a lot...works like a charm.. |
|
|
|
|
|
|
|
|
SQL database structure and query for school