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
 

 

Query required

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

New User


Joined: 16 Jul 2009
Posts: 5
Location: India

PostPosted: Fri Feb 14, 2014 2:57 pm    Post subject: Query required
Reply with quote

Hi Team,

I have a table that contains Name, Date of access, salary and Rate

Lets say table contains below values

Name Date of access Salary Rate
ABC 2014-02-13 111 0.5
CDE 2010-01-11 222 0.1
ABC 2010-01-01 333 0.2
ZZZ 2009-01-06 444 0.6
XXX 2014-01-30 555 0.1
CDE 2013-12-12 666 0.1

I want the output which select rows of latest date for a given name.
Output expected will be

Name Date of access Salary Rate
ABC 2014-02-13 111 0.5
ZZZ 2009-01-06 444 0.6
XXX 2014-01-30 555 0.1
CDE 2013-12-12 666 0.1

Help required in writing this DB2 query
Back to top
View user's profile Send private message

TheMFKid

New User


Joined: 20 Nov 2013
Posts: 91
Location: India

PostPosted: Fri Feb 14, 2014 3:11 pm    Post subject:
Reply with quote

post the query you have been trying and we will help you to get it right.
Back to top
View user's profile Send private message
Guru Nandu

New User


Joined: 16 Jul 2009
Posts: 5
Location: India

PostPosted: Fri Feb 14, 2014 3:34 pm    Post subject: Query required
Reply with quote

SELECT Name FROM Table where
Name = (SELECT Name FROM Table WHERE
Date of access = (SELECT MAX(Date of access ) FROM Table))
Back to top
View user's profile Send private message
sureshpathi10

Active User


Joined: 03 May 2010
Posts: 154
Location: Kuala Lumpur

PostPosted: Fri Feb 14, 2014 3:39 pm    Post subject:
Reply with quote

Can you try with this?

Code:
SELECT NAME, MAX(DATE OF ACCESS)
FROM TABLE
GROUP BY NAME
Back to top
View user's profile Send private message
Guru Nandu

New User


Joined: 16 Jul 2009
Posts: 5
Location: India

PostPosted: Fri Feb 14, 2014 3:57 pm    Post subject: Query required
Reply with quote

Hi Suresh,

It works with the query that you provided if i retriece name and Date of access.

However I require rate along with Name and date of access.

SELECT Name, Rate, MAX(Date of access)
FROM Table
GROUP BY Name, Rate
WITH UR;

I am getting duplicate rows having same name ABC.
Back to top
View user's profile Send private message
sureshpathi10

Active User


Joined: 03 May 2010
Posts: 154
Location: Kuala Lumpur

PostPosted: Mon Feb 17, 2014 8:05 am    Post subject:
Reply with quote

I'm guessing, Name and Date of access will be unique.

Here is the query.
Code:
SELECT *
FROM (SELECT NAME, MAX(DATE OF ACCESS) AS DATE_1 FROM TABLE GROUP BY NAME) A, TABLE B
WHERE A.NAME = B.NAME AND A.DATE_1 = B.DATE OF ACCESS


Please let us know if this working for you...
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
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts SQL Query optimization. arunsoods DB2 12 Sun Sep 04, 2016 6:27 am
No new posts Query on BLSR Nileshkul JCL & VSAM 2 Sat Aug 13, 2016 5:18 am


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