|
View previous topic :: View next topic
|
| Author |
Message |
Shweta12j
New User
Joined: 10 May 2010
Posts: 32
Location: Mumbai
|
|
|
|
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 |
|
 |
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
Come on  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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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: ibmmainframes.com/faq.php?mode=bbcode |
|
| Back to top |
|
|
|
|
