topic Re: Selecting most recent record using UZEIT in Application Development and Automation Discussions

Selecting most recent record using UZEIT

Former Member — Thu, 28 Oct 2010 12:12:58 GMT

Hi folks,

I have a select statement that fetches multiple records for same combinations where only UZEIT (time) is different. I would like to pick up the most recent record for each combination. Please advise.

Here is an example using 3 fields. This is the table to pick up the data from.

ZPROD1 11.11.2010 1:00 pm

ZPROD1 11.11.2010 2:00 pm

ZPROD1 11.11.2010 3:00 pm

ZPROD2 11.11.2010 1:00 pm

ZPROD2 11.11.2010 1:30 pm

These are the records that should get picked up (so the ones with the latest time).

ZPROD1 11.11.2010 3:00 pm

ZPROD2 11.11.2010 1:30 pm

Thanks in advance!!

Re: Selecting most recent record using UZEIT

Former Member — Thu, 28 Oct 2010 13:01:37 GMT

Hi,

I think you can use MAX keyword in your query.


select fld1 fld2 fld3 max(uziet) from .......

else if you want the latest record, then make use of the result of the mensioned query.

Thanks,

Anmol.

Re: Selecting most recent record using UZEIT

Former Member — Thu, 28 Oct 2010 13:05:46 GMT

Hi Anmol,

Thanks for the response but i should clarify that the data is stored in the internal table. I can't use the MAX keyword at the time of dumping data into this internal table since using the FOR ALL ENTRIES statement there.

To clarify, in my original post, the 1st set of contents are staged in an internal table and then I would like to get the max (or most recent in my case) time for each of those unique records.

Cheers!

Re: Selecting most recent record using UZEIT

Former Member — Thu, 28 Oct 2010 13:08:14 GMT

Hi,

You can try as :



"Say your data is in is IT_FINAL with fileds ZPROD DATE TIME

SORT IT_FINAL BY ZPROD DATE TIME DESCENDING.
DELETE ADJECENT DUPLICATES FROM IT_FINAL COMPARING ZPROD DATE.

LOOP AT IT_FINAL INTO WA_FINAL.
"Write your logic here
ENDLOOP.

Thanks & Regards,

Faheem.

Re: Selecting most recent record using UZEIT

Former Member — Thu, 28 Oct 2010 13:34:49 GMT

Awesome. Thank you folks!