topic Re: select max in Application Development and Automation Discussions

select max

MaDo — Tue, 30 Nov 2021 11:33:02 GMT

How to select max second-to-last in sap ABAP query?

Re: select max

Yasin — Tue, 30 Nov 2021 11:40:04 GMT

Hi Ahmad,

what do u mean by second-to-last please clarify more

Thanks

Re: select max

MaDo — Tue, 30 Nov 2021 11:55:01 GMT

For example, I have these

11384474

11333125

11648595

11648594

11405886

11354018

11648593

11648591

27547272

27547271

27547270

27547269

I want to get the max number but the second ( 27547270 )

Re: select max

Yasin — Tue, 30 Nov 2021 12:06:15 GMT

Hi Ahmad

you can try something like

SELECT MAX( VBRK~VBELN ) INTO ( v_VBELN )
  FROM VBRK
 WHERE VBRK~VBELN < ( SELECT MAX( VBRK~VBELN )
                 FROM VBRK ).

you may need to sort Ascending or Descending you can check base on result.

Please reward if was helpful to you.

Thanks.

Re: select max

MaDo — Tue, 30 Nov 2021 12:24:14 GMT

Gave me the last number 27547269 but I need the second last 27547270

SELECT MAX( CDPOS~CHANGENR ) INTO NLL
FROM CDPOS
WHERE CDPOS~CHANGENR < ( SELECT MAX( CDPOS~CHANGENR ) FROM CDPOS )
and TABNAME = 'MVKE'
AND FNAME = 'MVGR2'
and TABKEY LIKE '%100010'
AND CDPOS~OBJECTID = MARA-MATNR.

Re: select max

Yasin — Tue, 30 Nov 2021 12:34:00 GMT

i have tested my query it is working perfect. if it is giving last number then try to run the query without the

where condition only use the MAX what value will get ?

make sure that TABKEY LIKE '%100010' is not causing any conflict. try to run the query without many condition so you can trace

Re: select max

MaDo — Tue, 30 Nov 2021 13:21:04 GMT

where condition only use the MAX what value will get ?

Yes

Re: select max

Yasin — Tue, 30 Nov 2021 13:52:38 GMT

Dear Ahmad

in your list you have the value 27547272 which is bigger than 27547269 , 27547270 , 27547271

so the max value in your list is 27547272 the second last is 27547271

Thanks

Re: select max

Sandra_Rossi — Tue, 30 Nov 2021 14:52:20 GMT

Good candidate for a Window function (many blog posts), depends which ABAP version you're running. In older ABAP versions, you can still do it with classic ABAP code. I guess your query is more complex than the one you ask (max value of all lines of the table), so if you provide it, people could better answer.

Re: select max

former_member660513 — Tue, 30 Nov 2021 14:53:50 GMT

Did you try with

order by 'your_column' 
up to 
2 ROWS

and then read the second row of the result set?