Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

select Problem

Former Member
0 Kudos
179

Hi All,

I have a requirement in which i have to select belnr field from EKBE table on basis of ebeln and ebelp,but there are multiple records in EKBE table.i have to select the latest belnr number on basis of date field.how can i do that?

For ex

ebeln ebelp belnr date

45 10 001 07/10/2007

45 10 002 09/10/2007

45 10 003 10/10/2007

i want the third record with belnr 003.

thanks in advance

1 ACCEPTED SOLUTION

Former Member
0 Kudos
147

Hi,

One way to do this would be to get all the records for a particular ebeln and ebelp and then sort the records based on the date field in descending order.

Once this is done you will have the latest record on top and then use delete adjacent duplicates to delete all the other records except the first one which is the latest one.

Hope it is useful.

Thanks,

Sandeep.

8 REPLIES 8

Former Member
0 Kudos
147

Hi,

Check the syntax Of Select with Order keyword..

Order by ur required field (date)

Can be helpful..

Thanks,

Praveen

Message was edited by:

praveen parasiya

former_member386202
Active Contributor
0 Kudos
147

Hi,

Just check belnr ebeln ebelp and lastly posting date budat.

regards,

Prashant

andreas_mann3
Active Contributor
0 Kudos
147

use

select belnr max( date ) from ekbe into rec...

where ebeln = 45

and ebelp = 10

A.

Former Member
0 Kudos
147

You can do it in 2 ways.

First.. get all records in to internal table. Sort the ITAB by date DESCENDING. Now reading the first record with that BELNR. It wil hav less burden on database.

Second, you can get the data by using the <DATE field> DESCENDING in the SELECT statement. But as you are going for aggragate funstions the System will take more time to run this...

i.e. SELECT ....

ORDER BY <DATE FIELD> DESCENDING....

former_member200338
Active Contributor
0 Kudos
147

Hi,

Say IT_EKBE has the records.

sort it_ekbe by date descending.

read table it_ekbe with key ebeln = <<ur value>>

Reward point if useful,

Regards,

Niyaz

rainer_hbenthal
Active Contributor
0 Kudos
147

select

ebeln

ebelp

belnr

date

into (....)

from ekbe as o

where date = ( select max(date)

from ekbe

where o~ebeln = ebeln

and o~ebelp = ebelp

and o~belnr = belnr

) .

former_member404244
Active Contributor
0 Kudos
147

Hi,

U can do it in two ways..either get all the data into internal table and then u can use this ststemnt

sort itab by date descending..Now the latest reocrd will be the first record..

Or

write a select query like this

SELECT single * FROM ekbe into wa_ekbe WHERE ebeln = p_ebeln

and ebelp = p_ebelp

ORDER BY date DESCENDING.

Now u will get only the latest record.

rewrad if helpful.

Regards,

Nagaraj

Former Member
0 Kudos
148

Hi,

One way to do this would be to get all the records for a particular ebeln and ebelp and then sort the records based on the date field in descending order.

Once this is done you will have the latest record on top and then use delete adjacent duplicates to delete all the other records except the first one which is the latest one.

Hope it is useful.

Thanks,

Sandeep.