Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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: 6968
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: 2285
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: 6968
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 Fetch Previous month & year in MM... girishb2 DFSORT/ICETOOL 3 Thu Sep 21, 2017 9:54 pm
No new posts column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm
This topic is locked: you cannot edit posts or make replies. Limit access to certain RACF group cvnlynn CLIST & REXX 5 Wed Aug 23, 2017 2:28 am
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am
No new posts find RACF group for access to spooled... jzhardy JCL & VSAM 1 Mon May 08, 2017 11:46 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us