Select last record

[Former Member]

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...

[sushant_singh]

use the code as:

describe table <tabname> lines p_line.

read table <tabname> index p_line.

[Former Member]

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.

[Former Member]

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.

[Former Member]

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.

[Former Member]

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

[Former Member]

hi,

u can use 'FOR all entries'

chetan vishnoi

[Former Member]

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