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: 

Issue with select query with MAX keyword and Group by keyword.!

0 Kudos

I wrote a select query to get the latest record:

SELECT SINGLE MAX( obknr ) MAX( datum ) INTO (wa_obknr, wa_datum) FROM objk WHERE equnr = lv_equip-ast_asset_num AND taser = 'SER01' GROUP BY obknr datum.

The above query should get the latest record as shown in the image attached

But its fetching 1st record only not the second record. I need to fetch 2nd record

7 REPLIES 7

raymond_giuseppi
Active Contributor
0 Kudos

AFAIK you cannot use aggregate function on the group by fields, remove the max, use a order by clause and select up to 1 rows.

Sandra_Rossi
Active Contributor
0 Kudos

You didn't explain with words what you want to do, so impossible to understand what you want to achieve.

For information, this code compiles (in ABAP 7.57):

SELECT SINGLE MAX( obknr ), MAX( datum )
FROM objk 
WHERE equnr = '' AND taser = 'SER01'
INTO (@data(wa_obknr), @data(wa_datum)).

0 Kudos

sandra.rossi The above code should have fetched value with date: 24.02.2023, but its fetching the value with date: 09.01.2023

Sandra_Rossi
Active Contributor

I don't get what you mean, isn't it correct?

Sandra_Rossi
Active Contributor
0 Kudos

I guess I understand what you mean, but it's your screenshot which is wrong and doesn't illustrate at all what you mean.

0 Kudos

sandra.rossi So the solution is to not use GROUP BY keyword when we are using aggregate keywords( like : MAX).

Thank you Sandra.

Sandra_Rossi
Active Contributor

Is it a generalization that you are proposing for all cases? If so, NOT AT ALL.

If I understand well what you mean (you still didn't clarify), you are right that you must not use GROUP BY (but not because you are using MAX). Use either Raymond solution, or use a window expression, or use IN ( SELECT ... MAX ... ), etc. (many possibilities as you can see)

I guess you won't clarify so it could be something like:

SELECT SINGLE obknr, datum
    FROM objk
    WHERE equnr = '000000000100001024' AND taser = 'SER03'
      AND obknr IN ( SELECT MAX( obknr )
                    FROM objk AS objk_2
                    WHERE equnr = objk~equnr
                      AND taser = objk~taser )
    INTO (@DATA(wa_obknr), @DATA(wa_datum)).