topic Re: select latest record from a database table in Application Development and Automation Discussions

select latest record from a database table

Former Member — Mon, 10 Jul 2006 10:40:09 GMT

Hi,

How do you select the latest record from a databse table?

Regards

Nishant

Re: select latest record from a database table

dani_mn — Mon, 10 Jul 2006 10:42:15 GMT

SELECT MAX (document_number) FROM <table>

Re: select latest record from a database table

Former Member — Mon, 10 Jul 2006 10:43:24 GMT

IF YOU HAVE AN INCREMENTAL NUMBER IN YOUR TABLE YOU CAN USE..

SELECT
MAX( FIELDNAME )
INTO MYVARIABLE
FROM TABLE.

Also see this link ==>

RGDS,

Re: select latest record from a database table

Former Member — Mon, 10 Jul 2006 10:43:35 GMT

Unless you have a DATE and TIME field in the database table, there is no way of knowing which is the latest record. If you have those fields select all the fields that match your criteria, sort them in DESC manner of DATE and TIME and READ the first row.

Regards,

Ravi

Re: select latest record from a database table

Former Member — Mon, 10 Jul 2006 10:43:40 GMT

sort itab by field1 desending.

1st record is ur latest record.

ex--

sort itab1 by docno decending.

read table itab1 index 1.

Re: select latest record from a database table

Former Member — Mon, 10 Jul 2006 10:45:41 GMT

Select the record from the database table in the internal table.

Sort internal table.

READ TABLE itab INTO wa_itab INDEX 1.

You will get the latest record.

Message was edited by: mukesh kumar

Re: select latest record from a database table

Former Member — Mon, 10 Jul 2006 11:01:15 GMT

Hi Nishant,

You can use the option ORDER BY in your SELECT clause.

This is the SAP Documentation for ORDER BY option.

Variant 1

...ORDER BY PRIMARY KEY

Effect

Sorts the selected lines in ascending order by the primary key of the database table. This variant is only permitted for SELECT * ....

Notes

Since views do not have a primary key, specifying ORDER BY PRIMARY KEY only makes sense with database tables. If, however, you do specify ORDER BY PRIMARY KEY with a view, all fields of the view are sorted in ascending order.

Variant 2

ORDER BY f1 ... fn

Effect

Sorts the selected records in ascending order by the specified column references f1 ... fn. If a list is also specified in the SELECT clause, the column references f1, ..., fn must appear in this list.

By supplementing the statement with DESCENDING, you can sort in descending order using any of the fields f1, ..., fn.

The default sort sequence is ascending order, but you can make this explicit by adding the addition ASCENDING.

Consider this code this will select the latest 10 records form DB table(Note can use Variant 2 for DB tables also.


REPORT zarun_1.


DATA : it_mara TYPE STANDARD TABLE OF mara WITH HEADER LINE.

START-OF-SELECTION.

  SELECT * FROM mara
            INTO TABLE it_mara
            <b>UP TO 10 ROWS                "No of rows you need(give 1 here)
           ORDER BY matnr DESCENDING.    "Specify the Key Fields here.</b>

  LOOP AT it_mara.

    WRITE : / it_mara-matnr,
              it_mara-mtart.


  ENDLOOP.

Regards,

Arun Sambargi.

Message was edited by: Arun Sambargi

Re: select latest record from a database table

Former Member — Mon, 10 Jul 2006 11:23:20 GMT

Hi,

As far as I know there is no way you can select latest records from a table in SAP unless date & time field are specified. Transactional data doesn't have date & time fields unlike master data.

Try with Native SQL there may be some options from database end. with in SAP I don't think it is possible but try this option come from Master record for example if you want latest record in EKPO try to come from EKKO as there you have date field.

Regards,

Karthik.k

Re: select latest record from a database table

andreas_mann3 — Mon, 10 Jul 2006 12:46:04 GMT

hi,

or look

regards Andreas

Re: select latest record from a database table

andresperez818 — Fri, 19 May 2023 19:47:23 GMT

Great! it works. Thank you.