View previous topic :: View next topic
|
Author |
Message |
Debasis Misra Warnings : 1 New User
Joined: 16 Sep 2008 Posts: 72 Location: Bangalore
|
|
|
|
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 |
|
|
Bharath Bhat
Active User
Joined: 20 Mar 2008 Posts: 283 Location: chennai
|
|
|
|
SELECT section,marks
FROM table A
WHERE marks=(SELECT MAX(marks) from table B
where B.section=A.section)
Not tested. |
|
Back to top |
|
|
Cristopher
New User
Joined: 31 Jul 2008 Posts: 53 Location: NY
|
|
|
|
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 |
|
|
Debasis Misra Warnings : 1 New User
Joined: 16 Sep 2008 Posts: 72 Location: Bangalore
|
|
|
|
Thanks for the response...
what kind of example you want? |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 |
|
|
Debasis Misra Warnings : 1 New User
Joined: 16 Sep 2008 Posts: 72 Location: Bangalore
|
|
|
|
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 |
|
|
Cristopher
New User
Joined: 31 Jul 2008 Posts: 53 Location: NY
|
|
|
|
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 |
|
|
Debasis Misra Warnings : 1 New User
Joined: 16 Sep 2008 Posts: 72 Location: Bangalore
|
|
|
|
I have tried ...its not working!!!!!! |
|
Back to top |
|
|
Cristopher
New User
Joined: 31 Jul 2008 Posts: 53 Location: NY
|
|
|
|
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 |
|
|
Debasis Misra Warnings : 1 New User
Joined: 16 Sep 2008 Posts: 72 Location: Bangalore
|
|
|
|
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 |
|
|
Cristopher
New User
Joined: 31 Jul 2008 Posts: 53 Location: NY
|
|
|
|
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 |
|
|
Debasis Misra Warnings : 1 New User
Joined: 16 Sep 2008 Posts: 72 Location: Bangalore
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
Debasis Misra Warnings : 1 New User
Joined: 16 Sep 2008 Posts: 72 Location: Bangalore
|
|
|
|
Sorry Dick!!
i not get u!
come on Cris!!!! |
|
Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
|
|
|
|
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 |
|
|
Debasis Misra Warnings : 1 New User
Joined: 16 Sep 2008 Posts: 72 Location: Bangalore
|
|
|
|
Some misunderstanding, some requirement problem....it happens!!!!!!
Unproductive text removed. . . |
|
Back to top |
|
|
Cristopher
New User
Joined: 31 Jul 2008 Posts: 53 Location: NY
|
|
|
|
Logged in a little late
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 |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
|
|
|
|
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.
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
Cris,
I apologize for my sarcasism. |
|
Back to top |
|
|
|