|
View previous topic :: View next topic
|
| Author |
Message |
maxsubrat
Active User
Joined: 27 Feb 2008
Posts: 110
Location: india
|
|
|
|
Hi,
I want to fetch the latest record from a DB2 table based on one field PO create time stamp, thru an SQL query only.
Thanks
Subrat |
|
| Back to top |
|
 |
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK
|
|
|
|
If it by an SQL query only why did you post a request for a sort solution in the sort forum?
Possibly:
| Code: |
| Where TIMESTAMP = Max(Timestamp) |
but you will have to do your due diligence in checking that. |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
or you can say..
| Code: |
Order by PO create time stamp DESC
fetch first 1 row only |
|
|
| Back to top |
|
|
maxsubrat
Active User
Joined: 27 Feb 2008
Posts: 110
Location: india
|
|
|
|
Suppose my table structure is like below:
ACT-ID PO-NBR ACT-TS
12 12345 2015-12-02-10-00-00-000000
12 12345 2015-12-02-09-00-00-000000
12 12345 2015-12-02-08-00-00-000000
15 99999 2015-12-01-04-00-00-000000
15 99999 2015-12-01-09-00-00-000000
25 12345 2015-12-03-09-00-00-000000
25 88888 2015-12-01-09-01-00-000000
I want to fetch the latest ACT-TS for each PO-NBR and ACT-ID in (12,15) only.
so my output data should be:
ACT-ID PO-NBR ACT-TS
12 12345 2015-12-02-10-00-00-000000
15 99999 2015-12-01-09-00-00-000000 |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
| Code: |
Select acct-Id,po-nbr,max(act-to)
From table
Group by acc-Id,po-nbr
Order by 1,2 |
|
|
| Back to top |
|
|
maxsubrat
Active User
Joined: 27 Feb 2008
Posts: 110
Location: india
|
|
|
|
suppose I have this type of record structure:
ACT-ID PO-NBR ACT-TS
02 12222 2015-12-12-10-00-00-000000
02 12222 2015-12-15-10-00-00-000000
10 19222 2015-12-12-10-00-00-000000
12 12345 2015-12-02-10-00-00-000000
12 12345 2015-12-02-09-00-00-000000
12 12345 2015-12-02-08-00-00-000000
15 99999 2015-12-01-04-00-00-000000
15 99999 2015-12-01-09-00-00-000000
25 12345 2015-12-03-09-00-00-000000
25 88888 2015-12-01-09-01-00-000000 |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
Have tried first , what has given to you? Are you clear what would you want to achieve ?
If you want further help then provide us sample input, logic , output what yo want.
Add a Where condition to above sql,
| Code: |
| Where act-id in (12,15) |
|
|
| Back to top |
|
|
maxsubrat
Active User
Joined: 27 Feb 2008
Posts: 110
Location: india
|
|
|
|
| Thanks |
|
| Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK
|
|
|
|
| And PLEASE use the code tags when presenting data. |
|
| Back to top |
|
|
|
|
