topic Re: SQL: retrieve the most recent entry in Application Development and Automation Discussions

SQL: retrieve the most recent entry

tafkap95 — Tue, 17 Sep 2024 08:55:02 GMT

Hello,

I would like to retrieve the most recent entry of my data table at once.

Let's imagine I have the following table (a piece of the MSEG table):

ROWS	MATNR	CHARG	CPU_DATE	CPU_TIME
1	000012345	CA001	09.16.2024	14:00:00
2	000012345	CA002	09.16.2024	15:12:12
3	000012345	BO256	03.03.2024	23:12:00
4	000012345	CU123	09.17.2024	00:34:37
5	000012345	RA698	27.06.2024	08:00:00
6	000065887	TT0001	23.03.2023	05:32:00
7	000065887	YU3654	01.01.2024	07:12:00
8	000065887	PP5546	27.12.2023	15:23:00

For article 000012345 I wanted to retrieve line 4 from an internal table and for article 000065887 line 7, which are the most recent records by comparison of the CPU_DATE/CPU_TIME fields.

Thanks for any help.

Re: SQL: retrieve the most recent entry

RaymondGiuseppi — Tue, 17 Sep 2024 09:58:42 GMT

What did you already try, some subquery or ?

SELECT matnr, charg, cpudt_mkpf, cputm_mkpf FROM mseg AS m WHERE m~cpudt_mkpf EQ ( SELECT MAX( cpudt_mkpf ) FROM mseg WHERE matnr EQ m~matnr ) AND m~cputm_mkpf EQ ( SELECT MAX( cputm_mkpf ) FROM mseg WHERE matnr EQ m~matnr AND cpudt_mkpf = m~cpudt_mkpf ) INTO CORRESPONDING FIELDS OF TABLE @mytab.

Re: SQL: retrieve the most recent entry

Sandra_Rossi — Tue, 17 Sep 2024 10:05:39 GMT

Modern way (>= 7.54): window expression - ABAP Keyword Documentation (sap.com).

There's still the older way (join on itself via EXISTS, take MAX), simplified version: SELECT matnr, cpudate FROM mseg AS alias WHERE EXISTS IN ( SELECT matnr, MAX(cpudate) FROM mseg GROUP BY matnr WHERE matnr = alias~matnr).

Re: SQL: retrieve the most recent entry

tafkap95 — Tue, 17 Sep 2024 15:11:33 GMT

Hi Sandra,

I really like the idea of the Modern Way, it's always interesting to learn new features, I'll take a closer look at that.