September 16, 2005 at 12:26 am
I have a table named Tresult, i have detail description of all the schools as mentioned below:
Stu_Name School_name Tot_marks Top_state
Note: No field is primary key and i can't modify the structure of it.
i want to fetch details of top 5 states from the entire country based on their (totl_marks) and simultaneously top 2 schools for each topped state
Stu_Name School_name Tot_marks Top_state
John BPS 500 DEL
Peter HSS 499 DEL
Rose BVB 498 CHN
Martin CMV 499 CHN
Raju SXR 500 MUM
Chris NVS 450 MUM
Abraham MPS 435 RAJ
Daisy RJS 415 RAJ
Tom VNS 415 KAN
David FPA 400 KAN
Pls. help me as i am not getting data in this structure.
Neetu Sharma
September 16, 2005 at 12:57 am
What do you do if there is a tie for 5th place, i.e. two states with the same tot_marks, ranked 5th and 6th? Which one should be in the top 5?
September 16, 2005 at 3:37 am
Top 5 states are selected on the basis of the overall marks(all schools, all students of that particular state) of individual states.
If question comes of choosing between 5th and 6th for the top position based on overall marks .Lets take an example, two states r scoring equal tot_marks, so these two states r qualifying for 5th position. I'll see out of these two states whose top 2 students r scoring more marks.
For eg.
Stu_Name School Tot_marks Top_state
Tom VNS 415 KAN
David FPA 400 KAN
Gimi TGS 412 GOA
Wil PAL 403 GOA
In this case i'll choose KAN for 5th position bcoz maximum marks achieved by student belong to KAN, although sum of total_marks are same for KAN and GOA
Neetu Sharma
September 16, 2005 at 5:19 am
I this what you are looking for (in the top 5 case):
select top_state
from
(
select top 5 top_state, sum(tot_marks) as tot, max(tot_marks) as m from tresult
group by top_state
order by tot, m
)
dt
September 16, 2005 at 5:58 am
Thanx 4 sending me code but my query was to view top 5 states(which u have solved) and for each state their 2 top schools also. I have to present this result in the above mentioned structure.??
Neetu Sharma
September 16, 2005 at 6:09 am
This perhaps (and I forgot "desc" in my first query):
select top_state, stu_name from tresult t
where stu_name in (select top 2 stu_name from tresult where top_state = t.top_state order by tot_marks desc)
select top_state
from
(
select top 5 top_state, sum(tot_marks) as tot, max(tot_marks) as m from tresult
group by top_state
order by tot desc, m desc
)
dt
September 16, 2005 at 8:00 am
SELECT b.Top_state, b.Tot_State, c.School_name, c.Tot_marks AS [Tot_School]
FROM (
SELECT TOP 5 a.Top_state, SUM(a.Tot_marks) AS [Tot_State]
FROM Tresult a
GROUP BY a.Top_state
ORDER BY SUM(a.Tot_marks) DESC) b
INNER JOIN Tresult c ON c.Top_state = b.Top_state
AND c.School_name
IN (SELECT TOP 2 d.School_name
FROM Tresult d
WHERE d.Top_state = b.Top_state
ORDER BY d.Tot_marks DESC)
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply