topic Re: Data base select for latest record? in Application Development and Automation Discussions

Data base select for latest record?

Former Member — Mon, 04 Dec 2006 16:02:48 GMT

I have database Z-table

ZPRCTR ZGJAHR ZMONAT RPT_LOC

0000000330 2006 01 0330

0000000330 2006 02 0330

0000000330 2006 03 0330

0000000330 2006 04 0330

If I give ZPRCTR = '0000000330'

then I need to get the record

0000000330 2006 04 0330 which is latest one...because of ZMONAT = '04'.

Right now by using select single or select up to 1 rows

i am getting the oldest record.

0000000330 2006 <b>01</b> 0330

How can I achieve this ?

Re: Data base select for latest record?

Former Member — Mon, 04 Dec 2006 16:05:28 GMT

Hi,

When you use the SELECT ..

GIve ORDER BY ZMONAT DESCENDING in your select statement.

Example..

TABLES: BKPF.

SELECT * UP TO 1 ROWS

FROM BKPF

WHERE GJAHR = '2006'

ORDER BY MONAT DESCENDING.

ENDSELECT.

Thanks,

Naren

Re: Data base select for latest record?

Former Member — Mon, 04 Dec 2006 16:05:56 GMT

Hi,

Select Single of Select Upto one rows will give you the first record that is obtained by the where condition. It does not go further.

I think in this case, you need to select all and then sort descending and then delete adjacent duplicates comparing ZPRCTR ZGAHR ZMONAT.

Hope this helps....

Regards,

Shahu

Re: Data base select for latest record?

Former Member — Mon, 04 Dec 2006 16:06:20 GMT

select * from <table> into itab.

sort itab descending zmonat.

delete adjacent duplicates from itab comparing zmonat.

Re: Data base select for latest record?

Former Member — Mon, 04 Dec 2006 16:06:25 GMT

hi Sam,

do this way ..

sort itab by <field> <b>descending</b>.

and now use select single or upto 1 rows statement ..

Regards,

Santosh

Re: Data base select for latest record?

Former Member — Mon, 04 Dec 2006 16:10:22 GMT

Hi,

SORT ITAB BY FIELD_NAME DESCENDING.

Cheers.

Re: Data base select for latest record?

ferry_lianto — Mon, 04 Dec 2006 16:10:46 GMT

Hi Sam,

Please try this.


SELECT * UP TO 1 ROWS
FROM ZTABLE
WHERE ZGJAHR = '2006'
<b>ORDER BY ZPRCTR ZMONAT DESCENDING.</b>
ENDSELECT.

Hope this will help.

Regards,

Ferry Lianto

Re: Data base select for latest record?

Former Member — Mon, 04 Dec 2006 16:14:24 GMT

Hi,

i think you want to select the record with the maximum value of the field month, means the "oldest record".

Use the "max" Statement in your select:

select field1 field1 .... max (zmonat)

into structure

where field1 = ....

You will find description of max-statement in abap-online help.

regards!

Re: Data base select for latest record?

Former Member — Mon, 04 Dec 2006 18:02:59 GMT

Hi,

select * from <table> into itab.

sort itab by zprctr descending zmonat.

delete adjacent duplicates from itab comparing zprctr zmonat.

Then there will be only one entry(Recent entry) with perticular profit center.

Then read the Internal table by passing the profit center

Ex: read table itab with key zprctr = '0000000330'

-Sreenivas Reddy Maddi