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
 

 

Select distinct records with recent timsetamp

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

New User


Joined: 10 May 2010
Posts: 32
Location: Mumbai

PostPosted: Mon Feb 14, 2011 1:45 pm    Post subject: Select distinct records with recent timsetamp
Reply with quote

Hello All,

I have a query wherin the table structure is defined as below:

ACCNT_NO PIC 9(03).
TIMESTAMP PIC X(26).
MER_NO PIC 9(02) .

Now my requirement is to select a account no with recent timestamp for eg..


Account Timestamp Merch
---------------- -------------------------- --------------
123 2008-01-04-10.52.58.026034 35
123 2007-01-04-10.52.58.026034 35
124 2006-01-04-10.52.58.026034 35
124 2005-01-04-10.52.58.026034 35


I want to select only below rows :

Account Timestamp Merchant no
---------------- -------------------------- --------------
123 2008-01-04-10.52.58.026034 35
124 2006-01-04-10.52.58.026034 35


I tried to sort the result using distinct however I am not getting the above required result.

Is this possible using DB2 ..Please suggest!

Regards,
Shweta[/code]
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Mon Feb 14, 2011 2:32 pm    Post subject:
Reply with quote

Come on icon_rolleyes.gif this is basic SQL. I would remove DB2 from your acquired skills.

Code:
select * from table1 A
where timestamp = (select max(timestamp) from table1 B
           where A.account = b.account)
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Mon Feb 14, 2011 2:45 pm    Post subject:
Reply with quote

as a follow-up to GuyC's remark about non-existant db2 skills
Code:

ACCNT_NO       PIC 9(03).
TIMESTAMP      PIC X(26).
MER_NO         PIC 9(02)

is not a table structure, those are host-variable definitions.


there is no such thing as unsigned numeric in db2
and x(26) is 26 alpha-characters.
one needs to see the ddl to determine the datatype of each column.

and learn to use BBcode: http://ibmmainframes.com/faq.php?mode=bbcode
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 Removing Duplicates based on certain ... chandracdac DFSORT/ICETOOL 8 Fri Dec 09, 2016 4:40 am
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 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 How to split the records using the am... vnktrrd DFSORT/ICETOOL 24 Fri Oct 28, 2016 7:33 pm
No new posts Row-Numbers of distinct rows? Auryn DB2 1 Thu Oct 20, 2016 4:38 pm


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