topic Re: SQL SELECT in Application Development and Automation Discussions

SQL SELECT

Former Member — Tue, 11 Apr 2006 22:11:52 GMT

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)?

Re: SQL SELECT

RichHeilman — Tue, 11 Apr 2006 22:32:27 GMT

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

Re: SQL SELECT

Former Member — Tue, 11 Apr 2006 22:44:40 GMT

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.

Re: SQL SELECT

RichHeilman — Tue, 11 Apr 2006 22:54:47 GMT

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

Re: SQL SELECT

Former Member — Tue, 11 Apr 2006 23:24:46 GMT

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?

Re: SQL SELECT

Former Member — Wed, 12 Apr 2006 00:57:08 GMT

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>

Re: SQL SELECT

Former Member — Wed, 12 Apr 2006 02:25:35 GMT

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.

Re: SQL SELECT

Former Member — Wed, 12 Apr 2006 13:36:24 GMT

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?

Re: SQL SELECT

Former Member — Wed, 12 Apr 2006 15:16:21 GMT

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

Re: SQL SELECT

Former Member — Thu, 13 Apr 2006 02:43:31 GMT

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.