How to sort rows in "SELECT ... FOR ALL ENTRIES ...", ORDER BY is not accepted

Question

I am selecting a table that has multiple of the same records (same REQUEST_ID) with different VERSION_NO. So I want to sort it descending so I can take the highest number (latest record).

This is what I have...

IF it_temp2[] IS NOT INITIAL.
  SELECT request_id
         version_no
         status
         item_list_id
         mod_timestamp
  FROM ptreq_header INTO TABLE it_abs3
  FOR ALL ENTRIES IN it_temp2
  WHERE item_list_id EQ it_temp2-itemid.
ENDIF.

So version_no is one of the SELECT field but I want to sort that field (descending) and only take the first row.

I was doing some research and read that SORT * BY * won't work with FOR ALL ENTRIES. But that's just my understanding from reading up. Please let me know how I can make this work. Thanks

This is a classic [tag:greatest-n-per-group], with [many existing solutions](http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group). — Clockwork-Muse, Oct 04 '14 at 07:42

score 2 · Accepted Answer · answered Oct 06 '14 at 14:30

2

You can simply sort the itab after the select and delete all adjecent duplicates afterwards, if wanted:

SORT it_abs3 BY request_id [ASCENDING] version_no DESCENDING.
DELETE ADJACENT DUPLICATES FROM it_abs3 COMPARE request_id.

Depending on the amount of expected garbage (to be deleted lines) in the itab an SQL approach is better. See Used_By_Already's answer.

answered Oct 06 '14 at 14:30

Stefango

36
1

best and simplest answer for my situation. I sort request_id ascending and version_no descending inside my read table loop and it solved the prob. Thank you!! – ᴛʜᴇᴘᴀᴛᴇʟ Oct 06 '14 at 16:48

score 1 · Answer 2 · answered Oct 04 '14 at 05:55

If you are using the term "latest" to indicate "the most recent entry", then the field mod_timestamp appears to be relevant and you could use it this way to choose only the most recent records for each request_id.

SELECT
      request_id
    , version_no
    , status
    , item_list_id
    , mod_timestamp
FROM ptreq_header h
      INNER JOIN (
                  SELECT
                        request_id
                      , MAX(mod_timestamp) AS latest
                  FROM ptreq_header
                  GROUP BY 
                        request_id
            ) l
                  ON h.request_id = l.request_id
                        AND h.mod_timestamp = l.latest

If you want the largest version_no, then instead of MAX(mod_timestamp) use MAX(version_no)

score 0 · Answer 3 · answered Oct 04 '14 at 15:54

0

Just declare the it_abs3 as a sorted table with key that would consist of the columns you want to sort by.

You can also sort the table after the query.

SORT it_abs3 BY ...

answered Oct 04 '14 at 15:54

Jagger

10,350
9
51
93

How to sort rows in "SELECT ... FOR ALL ENTRIES ...", ORDER BY is not accepted

3 Answers3