IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Group by clause : Need to fetch all the coloumns


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Debasis Misra
Warnings : 1

New User


Joined: 16 Sep 2008
Posts: 72
Location: Bangalore

PostPosted: Wed Feb 11, 2009 1:10 pm
Reply with quote

There are 5 cols in the table.
4th col - Section
5th Col - marks.
I want to fetch all the coloumns for the section which are having max no in the section.

The following query gives me only the Section with max Marks.
SELECT
Section,
MAX(marks)
FROM Table
GROUP BY
Section;

Please help!!!!!!!!!!
Back to top
View user's profile Send private message
Bharath Bhat

Active User


Joined: 20 Mar 2008
Posts: 283
Location: chennai

PostPosted: Wed Feb 11, 2009 2:12 pm
Reply with quote

SELECT section,marks
FROM table A
WHERE marks=(SELECT MAX(marks) from table B
where B.section=A.section)

Not tested.
Back to top
View user's profile Send private message
Cristopher

New User


Joined: 31 Jul 2008
Posts: 53
Location: NY

PostPosted: Wed Feb 11, 2009 3:33 pm
Reply with quote

Debasis, Some examples from your side would certainly help us to provide you with the desired output.
Meanwhile you can try this query:
Code:
SELECT A.C1 , A.C2 , A.C3 , B.SECTION, B.HMARKS
FROM A , ( SELECT SECTION, MAX(MARKS) AS HMARKS FROM A
GROUP BY SECTION ) AS B
WHERE
A.SECTION = B.SECTION
A.MARKS = B.HMARKS


Cris
Back to top
View user's profile Send private message
Debasis Misra
Warnings : 1

New User


Joined: 16 Sep 2008
Posts: 72
Location: Bangalore

PostPosted: Thu Feb 12, 2009 10:02 am
Reply with quote

Thanks for the response...
what kind of example you want?
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Thu Feb 12, 2009 10:18 am
Reply with quote

Hello,

It will help if you post several sample rows of data from the table and the output you want when these rows are processed. Make sure your examples include all of the conditions you want dealt with for your requirement.
Back to top
View user's profile Send private message
Debasis Misra
Warnings : 1

New User


Joined: 16 Sep 2008
Posts: 72
Location: Bangalore

PostPosted: Thu Feb 12, 2009 10:40 am
Reply with quote

This a sample table :

Code:
Section    Dept      Subject         Marks
--------   ------    ---------      ----------
A            P         Math           20       
A            P         Physics        30
A            Q         Math           10
A            Q         Chemistry      40
A            R         Checmistry     25
B            P         English        35
B            Q         English        20
C            Q         Math           50
C            R         Physics        10
C            R         Math           30


Now I want fetch the records which having the max marks in a particular section.
So O/P should be :

Code:
Section    Dept      Subject          Marks
--------   ------    ---------      ----------
A            Q          Chemistry    40
B            P          English      35
C            Q          Math         50

Please let me know if the example is not clear.
Back to top
View user's profile Send private message
Cristopher

New User


Joined: 31 Jul 2008
Posts: 53
Location: NY

PostPosted: Thu Feb 12, 2009 12:15 pm
Reply with quote

Debasis, did you even try running the query provided to you earlier? Just by changing the select order you would have got the desired result,thus saving your time.Try this :
Code:
SELECT B.SECTION, A.DEPT,A.SUBJECT,B.HMARKS
FROM A , ( SELECT SECTION, MAX(MARKS) AS HMARKS FROM A
GROUP BY SECTION ) AS B
WHERE
A.SECTION = B.SECTION
A.MARKS = B.HMARKS


Cris
Back to top
View user's profile Send private message
Debasis Misra
Warnings : 1

New User


Joined: 16 Sep 2008
Posts: 72
Location: Bangalore

PostPosted: Thu Feb 12, 2009 2:40 pm
Reply with quote

I have tried ...its not working!!!!!!
Back to top
View user's profile Send private message
Cristopher

New User


Joined: 31 Jul 2008
Posts: 53
Location: NY

PostPosted: Thu Feb 12, 2009 3:12 pm
Reply with quote

Quote:
I have tried ...its not working!!!!!!

What is not working ??..is it throwing some error ...or not able to fetch the desired results ...as the query worked perfectly for me..you need to provide us with some input to work on.

Can you post the query you actually fired?

Cris
Back to top
View user's profile Send private message
Debasis Misra
Warnings : 1

New User


Joined: 16 Sep 2008
Posts: 72
Location: Bangalore

PostPosted: Thu Feb 12, 2009 3:30 pm
Reply with quote

No error!!!!! Not the desired result..

This query is not working in the following schenario:

If under the section A, with the Highest marks(40) 2 records are there, then I want to fetch first record only. But it is fetching both the records.
Back to top
View user's profile Send private message
Cristopher

New User


Joined: 31 Jul 2008
Posts: 53
Location: NY

PostPosted: Thu Feb 12, 2009 4:09 pm
Reply with quote

Quote:
This query is not working in the following scenario:
If under the section A, with the Highest marks(40) 2 records are there, then I want to fetch first record only.

Was this scenario touched upon earlier ...in any of your previous posts/example. NO.
What do you understand by the term "first record" ....is there anything like first or second record in DB2.I doubt.
This way you end up fetching different records for the same select query.

Please get your requirements clear before you expect a definitive advise.

Cris
Back to top
View user's profile Send private message
Debasis Misra
Warnings : 1

New User


Joined: 16 Sep 2008
Posts: 72
Location: Bangalore

PostPosted: Thu Feb 12, 2009 4:51 pm
Reply with quote

Ok!!! forget about the first record...
Any one of the record needs to fetch!!!!!!

Now the requirement is clearDo you have any solution for this ?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Feb 12, 2009 4:55 pm
Reply with quote

come on Cris, give the OP a solution for the task!

By the way, Debasis Misra,

no records, no fields in db2......rows and columns.
Back to top
View user's profile Send private message
Debasis Misra
Warnings : 1

New User


Joined: 16 Sep 2008
Posts: 72
Location: Bangalore

PostPosted: Thu Feb 12, 2009 5:42 pm
Reply with quote

Sorry Dick!!
i not get u!
come on Cris!!!! icon_lol.gif
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Thu Feb 12, 2009 6:25 pm
Reply with quote

Debasis , you sound as if Cris is being paid by you for helping you out ... in your cursor check for section and marks with the previous values ..if they are same discard the 2nd or 3rd or how many you want ... you can use order by section,marks .... do a lil bit of thinking ...
Back to top
View user's profile Send private message
Debasis Misra
Warnings : 1

New User


Joined: 16 Sep 2008
Posts: 72
Location: Bangalore

PostPosted: Fri Feb 13, 2009 12:54 am
Reply with quote

Some misunderstanding, some requirement problem....it happens!!!!!!

Unproductive text removed. . .
Back to top
View user's profile Send private message
Cristopher

New User


Joined: 31 Jul 2008
Posts: 53
Location: NY

PostPosted: Fri Feb 13, 2009 1:19 pm
Reply with quote

Logged in a little late icon_smile.gif
Quote:
come on Cris, get off your ass and give the OP a solution for the task!
is this what you call a productive text. I believe it should have been removed...
I thought by now dbz would have provided a solution...little disappointed.
Stop pocking your nose dude...you may be far better ...but it doesnt give u a right to spit where ever you wish....keep it to yourself.
Let us keep things simple, i admire people over here ...Dick,Robert any many more ....but i am not here to take shit from anyone.
Mind your own business and do your work..if you can help...good enough...donot dictate terms...
This is my last post in this forum and i will appreciate if going forward people are judged and driven by the same stick.

Thanks,
Cris
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Fri Feb 13, 2009 1:53 pm
Reply with quote

Cris,

Dont get dishearted. I think there was some sort of misunderstanding happened over here. We are here to help each other and you are on the right track. So keep posting. icon_smile.gif

Debasis Misra,

I am not sure what kind of testing you did. But the solution posted by Cris works perfectly fine and gives exactly the same output as shown by your expected output though I had to correct the syntax a bit.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Feb 13, 2009 1:54 pm
Reply with quote

Cris,

I apologize for my sarcasism.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Fetch data from programs execute (dat... DB2 3
No new posts To search DB2 table based on Conditio... DB2 1
No new posts Code Multi Row fetch in PL1 program PL/I & Assembler 1
No new posts Compare latest 2 rows of a table usin... DB2 1
No new posts Need to fetch data from so many DB2 t... DB2 9
Search our Forums:

Back to Top