SQL SELECT

[Former Member]

I am trying to do a sql select like "SELECT TOP 1 FROM..."

My table has different versions of orders. The order# may be 12345 and there may be 5 versions - 1 through 5. If I want to select a lot of orders, and only select the latest version, I want to select the latest version. For some orders, the latest version may be 3, for others it may be 2, etc. How can I construct a SQL select in ABAP to select only the latest version for each order (highest version number)?

[RichHeilman]

You will have to get all of them to start.

Select * into table itab
       from ztable 
             where.....

Then sort the itab by order number and version descending.

sort itab by vbeln ascending
             version descending.

Now delete the adjacent duplicates. This should delete all others after the first one.

delete adjacent duplicates from itab
               comparing vbeln.

Regards,

Rich Heilman

[Former Member]

I did that. The issue is, this table is very large. I am using this in a Netweaver BSP application, and we are having performance issues because it is selecting a large number of records.

[RichHeilman]

Or, another way to go is to somehow get the relevant order numbers, then hit that big table one at a time and get the versions for each order, then sort it, then read the first record of the itab. Or you can do a select single and use the order by extension to get the last one.

Regards,

Rich Heilman

[Former Member]

It will be difficult without knowing the structure of the table. Are these fields key fields or is at least one of them, a key field? How are the different versions inserted into the table? Is there a header-item kind of relationship or is it all in one table? Standard or custom?

[Former Member]

The fields are key fields. It is all one table. For example, the ones in bold/italic are what I would want to select.

Order#  Version
12345      1
12345      2
<i><b>12345      3</b></i>
67890      1
<i><b>67890      2</b></i>
<i><b>55555      1</b></i>
33332      1
<i><b>33332      2</b></i>

[Former Member]

Hi Denise,

You can use SQL aggregate functions.

TABLES: ztable.
DATA itab LIKE TABLE OF ztable WITH HEADER LINE.

SELECT ordernum max( version )
  FROM ztable
  INTO TABLE itab
  GROUP BY ordernum.

LOOP AT itab.
  WRITE: / itab-ordernum, itab-version.
ENDLOOP.

Ps: Reward points if helpful.

Regards,

Wenceslaus.

[Former Member]

This helps. What if I want to select corresponding values from the table. Can I do that. For example, if I have this data:

order#   vers   status
12345      1      S
12345      2      P
12345      3      A
88888      1      S
88888      2      P

I want to return:

order#   vers   status
12345      3      A
88888      2      P 

Is there any way to do that?

[Former Member]

Try this(assuming you always want the highest version which is also the latest)

SORT itab BY order# ASCENDING vers DESCENDING.

DELETE ADJACENT DUPLICATES FROM itab COMPARING order#.

[Former Member]

Hi Denise,

It wouldn't work if you have more than one column to select.

You can add one more select query to fetch the rows for the corresponding maximum values.

TABLES: ztest.
DATA itab LIKE TABLE OF ztest WITH HEADER LINE.
DATA itab2 LIKE TABLE OF ztest WITH HEADER LINE.

SELECT ordernum max( version )
  FROM ztest
  INTO TABLE itab
  GROUP BY ordernum.

SELECT ordernum version status
  FROM ztest
  INTO TABLE itab2
  FOR ALL ENTRIES IN itab
  WHERE ordernum = itab-ordernum
    AND version  = itab-version.

LOOP AT itab2.
  WRITE: / itab2-ordernum, itab2-version, itab2-status.
ENDLOOP.

Regards,

Wenceslaus.