Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Topper from the table

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
psriv20
Currently Banned

New User


Joined: 25 May 2009
Posts: 19
Location: Pune

PostPosted: Wed Jun 16, 2010 4:49 pm    Post subject: Topper from the table
Reply with quote

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
View user's profile Send private message

enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10211
Location: italy

PostPosted: Wed Jun 16, 2010 5:19 pm    Post subject: Reply to: Topper from the table
Reply with quote

topper? what' s that, and relative to what?

if You want good answers You must learn to post good questions...
see
http://catb.org/~esr/faqs/smart-questions.html
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Wed Jun 16, 2010 5:25 pm    Post subject:
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Wed Jun 16, 2010 5:34 pm    Post subject:
Reply with quote

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
View user's profile Send private message
psriv20
Currently Banned

New User


Joined: 25 May 2009
Posts: 19
Location: Pune

PostPosted: Thu Jun 17, 2010 3:13 pm    Post subject: Reply to: Topper from the table
Reply with quote

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
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Thu Jun 17, 2010 3:16 pm    Post subject:
Reply with quote

Sigh! have you read what GuyC has shown to you. One can not get better than that, for such a question... icon_neutral.gif
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Jun 17, 2010 7:39 pm    Post subject:
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Fri Jun 18, 2010 2:37 pm    Post subject:
Reply with quote

Anuj Dhawan wrote:
Sigh! have you read what GuyC has shown to you. One can not get better than that, for such a question... icon_neutral.gif

Why do I bother ? icon_wink.gif
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Jun 18, 2010 7:30 pm    Post subject: Reply to: Topper from the table
Reply with quote

Hi Guy,

Quote:
Why do I bother ?
'Cause even if the TS does not read/use the solution, others do. . . icon_cool.gif

d
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
No new posts Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm
No new posts Command UNIQUE CONSTRAINT on any tabl... rohanthengal DB2 2 Thu Aug 18, 2016 3:48 pm
No new posts Updating online Db2 table kishpra DB2 3 Fri Aug 12, 2016 2:44 pm
No new posts Getting soc4 on internal table in cobol abdulrafi COBOL Programming 13 Fri May 06, 2016 3:39 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us