Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref

Author Message
psriv20
Currently Banned

New User

Joined: 25 May 2009
Posts: 19
Location: Pune

 Posted: Wed Jun 16, 2010 4:49 pm    Post subject: Topper from the table 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

enrico-sorichetti

Global Moderator

Joined: 14 Mar 2007
Posts: 10457
Location: italy

 Posted: Wed Jun 16, 2010 5:19 pm    Post subject: Reply to: Topper from the table 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
Anuj Dhawan

Senior Member

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

 Posted: Wed Jun 16, 2010 5:25 pm    Post subject: 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?
GuyC

Senior Member

Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

Posted: Wed Jun 16, 2010 5:34 pm    Post subject:

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.
psriv20
Currently Banned

New User

Joined: 25 May 2009
Posts: 19
Location: Pune

 Posted: Thu Jun 17, 2010 3:13 pm    Post subject: Reply to: Topper from the table 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.
Anuj Dhawan

Senior Member

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

 Posted: Thu Jun 17, 2010 3:16 pm    Post subject: Sigh! have you read what GuyC has shown to you. One can not get better than that, for such a question...
dick scherrer

Site Director

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

Posted: Thu Jun 17, 2010 7:39 pm    Post subject:

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?
GuyC

Senior Member

Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

Posted: Fri Jun 18, 2010 2:37 pm    Post subject:

 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 ?
dick scherrer

Site Director

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

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

Hi Guy,

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

d
 All times are GMT + 6 Hours
 Page 1 of 1

Search our Forum:

 Topic Author Forum Replies Posted Similar Topics Searching a table for the field name? socker_dad COBOL Programming 8 Sat Mar 31, 2018 2:57 am Need inputs on DB2 tablespace/table r... ashek15 DB2 3 Sat Mar 24, 2018 11:49 pm COBOL declaration for table Poha Eater DB2 3 Mon Feb 26, 2018 6:58 pm load and delete table through JCL wit... ram_vizag JCL & VSAM 9 Thu Feb 01, 2018 12:47 am Unloading DB2 table in CSV format alo... grvtomar COBOL Programming 7 Sun Jan 28, 2018 2:05 am

 © 2003-2017 IBM MAINFRAME Software Support Division
 Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us