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
 

 

Group by clause : Need to fetch all the coloumns

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Group by clause : Need to fetch all the coloumns
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    Post subject:
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    Post subject: Reply to: Group by clause : Need to fetch all the coloumns
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    Post subject:
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

Site Director


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

PostPosted: Thu Feb 12, 2009 10:18 am    Post subject:
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    Post subject:
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    Post subject: Reply to: Group by clause : Need to fetch all the coloumns
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    Post subject:
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    Post subject: Reply to: Group by clause : Need to fetch all the coloumns
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    Post subject:
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    Post subject: Reply to: Group by clause : Need to fetch all the coloumns
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject: Reply to: Group by clause : Need to fetch all the coloumns
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: 2165
Location: @my desk

PostPosted: Fri Feb 13, 2009 1:53 pm    Post subject:
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    Post subject:
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    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 abend sort based on count records in ... anatol DFSORT/ICETOOL 5 Mon Oct 17, 2016 10:10 pm
No new posts Get Record count in summary record fo... Atul Banke DFSORT/ICETOOL 21 Fri Sep 23, 2016 4:17 pm
No new posts Multi row fetch - "for read only... Nileshkul DB2 3 Sun Aug 14, 2016 12:52 am
No new posts Need help on SQL Dynamic WHERE Clause subratarec DB2 12 Sat Jul 16, 2016 3:11 pm
No new posts Select first 2 group data vice_versa DFSORT/ICETOOL 10 Mon Jun 13, 2016 2:21 pm


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