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
 
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: 6
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: 6
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: 6
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
No new posts Build SQL query using SORT balaji81_k DFSORT/ICETOOL 3 Mon Feb 18, 2019 9:56 am
No new posts General Query on DB2 table design subratarec DB2 3 Thu Feb 14, 2019 12:49 pm
No new posts Query on summing up the fields abdulrafi DFSORT/ICETOOL 10 Mon Jan 21, 2019 7:18 pm
No new posts Query on secondary index Suja.Sai IMS DB/DC 1 Wed Oct 31, 2018 9:47 pm
No new posts Query regarding STOP REGION XX ABDU... ashek15 IMS DB/DC 11 Fri Oct 19, 2018 10:13 am

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