|
View previous topic :: View next topic
|
| Author |
Message |
psriv20
Currently Banned
New User
Joined: 25 May 2009
Posts: 19
Location: Pune
|
|
|
|
Suppose we have a table with 50 students name..and 5 subjects....How can we get topper's name from this table?
std1 55 60 45 50 60
std2 40 76 85 75 55
std3 80 60 95 65 70
...
...
std50 80 90 85 75 50
Please suggest me what will be query to get topper?
Thanks |
|
| Back to top |
|
 |
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
|
|
| To start with - what logic do you think should work here? And have you tried something, what happened - it did not work; didn't give the "topper"? Does the numbers in your post shows the makrs related to different subjects? |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
if by topper you mean a student with the highest total score on the five subjects :
| Code: |
select name, (sc1 + sc2 + sc3 + sc4 + sc5) as tot_score from TAB1
order by 2 desc
fetch first 1 rows only |
but when two student have the same total_score : only 1 will be shown.
| Code: |
select name, (sc1 + sc2 + sc3 + sc4 + sc5) as tot_score from TAB1
where
(sc1 + sc2 + sc3 + sc4 + sc5) = (select max(sc1 + sc2 + sc3 + sc4 + sc5) from tab1) |
will show all students who have the highest score
Actually this is a good opportunity to test the new OLAP functions :
| Code: |
select
name
, (sc1 + sc2 + sc3 + sc4 + sc5) as tot_score
, ROW_NUMBER () over (order by (sc1 + sc2 + sc3 + sc4 + sc5) DESC) as RN
, RANK () over (order by (sc1 + sc2 + sc3 + sc4 + sc5) DESC) as Rnk
, DENSE_RANK () over (order by (sc1 + sc2 + sc3 + sc4 + sc5) DESC) as Dense_Rnk
from TAB1 |
this last query wil result in something like this
| Code: |
name tot_score rn rnk Dens_rnk
std4 360 1 1 1
std2 360 2 1 1
std7 350 3 3 2
std6 340 4 4 3
std1 340 5 4 3
std8 330 6 6 4 |
so depending on what you want, you can write :
| Code: |
select
name
, (sc1 + sc2 + sc3 + sc4 + sc5) as tot_score
having ROW_NUMBER () over (order by (sc1 + sc2 + sc3 + sc4 + sc5) DESC) = 1
from TAB1 |
but I wouldn't recommend using olap_functions for something which can be solved easily as shown in the first two selects, because performance of olap functions aren't very good. |
|
| Back to top |
|
|
psriv20
Currently Banned
New User
Joined: 25 May 2009
Posts: 19
Location: Pune
|
|
|
|
I think i have to give example...let us suppose we have following statistics with us.
Name Subject Mark1 Mark2
A Math 24 20
B English 22 28
C Hindi 30 10
I want a query which will give anser as Name=B
Because if you add marks for a student
A=44(24+20)
B=50(22+28)
C=40(30+10)
if you see above details you will find that answer should be B.
But i dont know how to extract it with the help of db2 query.Kindly suggest me. |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
|
|
Sigh! have you read what GuyC has shown to you. One can not get better than that, for such a question...  |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| But i dont know how to extract it with the help of db2 query.Kindly suggest me. |
How does your request differ from the posted code? |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| Anuj Dhawan wrote: |
| Sigh! have you read what GuyC has shown to you. One can not get better than that, for such a question... |
Why do I bother ?  |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hi Guy,
'Cause even if the TS does not read/use the solution, others do. . . 
d |
|
| Back to top |
|
|
|
|
