topic Re: Select last record in Application Development and Automation Discussions

Select last record

Former Member — Tue, 25 Jul 2006 09:02:37 GMT

Hi ALL,

i need select last record for each MATNR from table EKPO.

I try this:

SELECT-OPTIONS:

mater FOR ekpo-matnr.

SELECT *

INTO CORRESPONDING FIELDS OF TABLE intab

FROM ekpo

WHERE matnr IN mater

ORDER BY aedat DESCENDING.

But I need just last record for each matnr.

Thanks for help...

Re: Select last record

sushant_singh — Tue, 25 Jul 2006 09:05:54 GMT

use the code as:

describe table <tabname> lines p_line.

read table <tabname> index p_line.

Re: Select last record

Former Member — Tue, 25 Jul 2006 09:09:59 GMT

You could write a SELECT ENDSELECT as given below order by the primary key or an unique value.

e.g----

SELECT *

INTO wa_intab

FROM ekpo

WHERE matnr IN mater

ORDER BY aedat DESCENDING.

endselect.

SELECT: *

from kna1

into wa_kna1

order by kunnr descending.

ENDSELECT.

Re: Select last record

Former Member — Tue, 25 Jul 2006 09:11:21 GMT

Hi vaclav,

1. do like this

2 just copy paste

3. internal table INTAB contains all records,

<b> ITAB contains only one record per matnr.

(The LATEST RECORD AS PER AEDAT)</b>

report abc.

tables : ekpo.

DATA : INTAB LIKE TABLE OF EKPO WITH HEADER LINE.

DATA : ITAB LIKE TABLE OF EKPO WITH HEADER LINE.

SELECT-OPTIONS:

mater FOR ekpo-matnr.

SELECT *

INTO CORRESPONDING FIELDS OF TABLE InTAB

FROM ekpo

WHERE matnr IN mater.

*----

sort intab by matnr aedat descending.

*----

loop at intab.

ON CHANGE OF INTAB-MATNR.

MOVE-CORRESPONDING INTAB TO ITAB.

APPEND ITAB.

ENDON.

endloop.

BREAK-POINT.

regards,

amit m.

Re: Select last record

Former Member — Tue, 25 Jul 2006 09:11:43 GMT

data: count type i.

SELECT *

INTO CORRESPONDING FIELDS OF TABLE intab

FROM ekpo

WHERE matnr IN mater

Describe table intab lines count.

read table itab index count.

write count.

Re: Select last record

Former Member — Tue, 25 Jul 2006 09:11:43 GMT

SELECT-OPTIONS:

mater FOR ekpo-matnr.

SELECT *

INTO CORRESPONDING FIELDS OF TABLE intab

FROM ekpo

WHERE matnr IN mater

ORDER BY aedat DESCENDING.

sort intab by mater aedat.

loop at intab.

at last aedat.

you will have the last record for each material here

endat.

endif.

the only thing needed here is that mater should be the first field in the intab and aedat the second field.

regards,

ravi

Re: Select last record

Former Member — Tue, 25 Jul 2006 09:12:21 GMT

hi,

u can use 'FOR all entries'

chetan vishnoi

Re: Select last record

Former Member — Tue, 25 Jul 2006 09:53:45 GMT

Hi Vaclav

I suppose you could ase use some sort of variation on:

SELECT *
       INTO CORRESPONDING FIELDS OF TABLE intab
       FROM ekpo AS e
      WHERE e~matnr IN mater
        AND aedat EQ ( SELECT MAX( aedat ) FROM ekpo
                              WHERE matnr EQ e~matnr ) .

This would select all the line items created on the latest date, though, so you could still get multiple lines per material. I'm not sure how efficient this would be, however, since the subquery is not selecting on key fields.

Cheers

Lyal