Re: SQL Question ... how to SETGT, READPE in SQL

[bill.blalock@xxxxxxxxxxx]

Ed:

Lets say the table (database) contains these fields

        id
        time_stamp
        data_field_1
        data_field_2
        data_field_3

I could not make the example get the last record, that is data_field_1, 
data_field_2, data_field_3 for the most recent record for each id.

Any thoughts?






eegbert@xxxxxxxxxxxxxxxxxx
07/26/2007 04:09 PM
Please respond to Java Programming on and around the iSeries / AS400
 
        To:     Java Programming on and around the iSeries / AS400 
<java400-l@xxxxxxxxxxxx>
        cc:     (bcc: Bill Blalock/TUS/US/Certegy)
        Subject:        Re: SQL Question ... how to SETGT, READPE in SQL


You could try: 
SELECT id, MAX(time_stamp)  FROM  table_name 
GROUP BY id 
This electronic message transmission contains information from Black Hills 

Corporation, its affiliate or subsidiary, which may be confidential or 
privileged.  The information is intended to be for the use of the 
individual or entity named above.   If you are not the intended recipient, 

be aware the disclosure, copying, distribution or use of the contents of 
this information is prohibited.   If you have received this electronic 
transmission in error, please notify us by telephone (605-721-2100) 
immediately. 
Ed Egbert 
Black Hills Corp.  IS division 
1140 Plant Street 
Rapid City, SD 57709 
(605) 721-2137 
"The only ones among you who will be really happy are those who have 
sought and  found how to serve." 
  -- Albert Schweitzer 



bill.blalock@xxxxxxxxxxx 
Sent by: java400-l-bounces@xxxxxxxxxxxx 
07/26/2007 03:03 PM 
Please respond to 
Java Programming on and around the iSeries / AS400 
<java400-l@xxxxxxxxxxxx> 

To 
java400-l@xxxxxxxxxxxx 
cc 
Subject 
SQL Question ... how to SETGT, READPE in SQL 





Is there a simple way to select the last record in a series?  Say the most 

recent record of a set using a timestamp. 
For example, a data base  record with 
        id 
        time_stamp 
        data fields 
In RPG one could create a logical file, or OPNQRYF with keys 
        id 
        time_stamp 
To get the last record for each id one could 
        set id to *LOVAL 
        repeat until end of file 
                SETGT 
                READPE 
Can you do this with SQL (hence with Java) ... or should you drop back to 
relative record access with JT400? 
Thanks all 
------------------------------------------------------------------------------ 

This message contains information from Certegy, Inc which may be 
confidential and privileged.  If you are not an intended recipient, please 

refrain from any disclosure, copying, distribution or use of this 
information and note that such actions are prohibited.  If you have 
received this transmission in error, please notify by e:mail 
postmaster@xxxxxxxxxxxx 
==============================================================================