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
 

 

Can we write a single query for this data?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
maverick05

New User


Joined: 14 Apr 2005
Posts: 54
Location: Earth

PostPosted: Mon Apr 20, 2009 8:33 am    Post subject: Can we write a single query for this data?
Reply with quote

Hi,

I have the following 3 rows in db2 table.Table has 3 columns X,Y,Z

X Y Z
.........

A 1 2001-01-01
A 2 2001-01-01
A 3 2002-02-02

Now my req is that i need to fetch the row with max Y column value(max(Y)) if and only if date 2001-01-01 is present in any of the 3 rows(Note that column1 value is same for all the 3 rows)

How can i achieve the result using a simple query.if not possible with a single query,what is the best query can be written to get the desired output.

so my output should be the last row with max column Y value .i.e 3

Thanks


[/img]
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: Mon Apr 20, 2009 8:43 am    Post subject:
Reply with quote

Hello,

What is the total number of rows in the entire table?

Are there any other rows with colX = A?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Mon Apr 20, 2009 3:56 pm    Post subject:
Reply with quote

maverick05,

you posted this same question on another website; also received an answer there.
Back to top
View user's profile Send private message
maverick05

New User


Joined: 14 Apr 2005
Posts: 54
Location: Earth

PostPosted: Mon Apr 20, 2009 5:55 pm    Post subject: Reply to: Can we write a single query for this data?
Reply with quote

Hi,

There can be more rows with column X value as 'A'. Basically the query should check for one row(date check) and should fetch some other row(max Y)





I posted the same question in diff forums at the same time as i was needing the answer asap.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Mon Apr 20, 2009 8:12 pm    Post subject:
Reply with quote

So did you get your ans finally ?
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: Mon Apr 20, 2009 8:38 pm    Post subject:
Reply with quote

Hello,

If the question was as incomplete on the other forums, no telling what kind of answers might have been given. . .

Quite possibly something that will appear to work, but actually does not for all cases. . .

Far too many do not understand that it is critical that the requirement be well presented and not just "thrown together" because they are in a hurry. . .
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Mon Apr 20, 2009 9:00 pm    Post subject:
Reply with quote

The OP received an answer on the other site,
OP's response was I don't think that will work.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Mon Apr 20, 2009 9:12 pm    Post subject:
Reply with quote

Assuming column X will have values other than A .... one query would be

Code:


select x,max(y) from table a
where exists ( select * from table where
z = '2001-01-01' and x = a.x ) group by x



You can even use a self join ...
Back to top
View user's profile Send private message
maverick05

New User


Joined: 14 Apr 2005
Posts: 54
Location: Earth

PostPosted: Mon Apr 20, 2009 11:35 pm    Post subject: Reply to: Can we write a single query for this data?
Reply with quote

The qbove query worked.thanks alot
Back to top
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA

PostPosted: Tue Apr 21, 2009 8:02 am    Post subject:
Reply with quote

maverick05, were you planning on letting folks know on BOTH boards that you found a solution on one of them, or were you planning on being inconsiderate of other peoples time?
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 Replace values in the input data Vikas Maharnawar DFSORT/ICETOOL 10 Thu May 11, 2017 2:18 pm
No new posts Group Data based on a key Arun Raj DFSORT/ICETOOL 7 Thu Apr 27, 2017 11:29 pm
No new posts unload data from table with lob columns farhad_evan DB2 0 Sat Apr 22, 2017 1:32 pm
This topic is locked: you cannot edit posts or make replies. SDSF multiple spool datasets extracte... PJAlarcon CLIST & REXX 1 Fri Apr 21, 2017 10:50 pm
No new posts SORT JSON type of data maxsubrat DFSORT/ICETOOL 8 Wed Apr 19, 2017 6:01 pm


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