Skip to Content
0
Jul 10 at 11:39 AM

Top N records with max value for each group of grouped SQL query

261 Views Last edit Jul 10 at 03:55 PM 3 rev

Simplest example:

From ZUSAGE table with records of APPID usage on date UDATE by user UNAME:

ID APPID UNAME UDATE
01 AP1   DEV   09.07.20
02 AP1   GQAUY 10.07.20
03 AP1   DEV   10.07.20
04 AP2   DEV   10.07.20
05 AP1   GMAL  10.07.20
06 AP1   PCHL  10.07.20
07 AP1   ADUL  10.07.20
08 AP1   DEV   08.07.20
09 AP1   GMAL  08.07.20
10 AP2   GMAL  08.07.20
11 AP2   ADUL  08.07.20

With this query:

select appid, uname, count(*) as total 
from zusage
group by appid, uname
order by appid, total descending
into corresponding fields of table @lt_usage.

I get all the users ranked by the highest number of usages:

ID   UNAME TOTAL
AP1  DEV   3
AP1  GMAL  2
AP1  ADUL  1
AP1  PCHL  1
AP1  GQAUY 1
AP2  DEV   1
AP2  ADUL  1
AP2  GMAL  1

As there is a vast number of users and apps and the query is frequent, I would like to get a list limited to, let’s say, the top 3 users for each app:

ID   UNAME TOTAL
AP1  DEV   3
AP1  GMAL  2
AP1  ADUL  1
AP2  DEV   1
AP2  ADUL  1
AP2  GMAL  1

All the solutions on the net are database-specific, and I could not find a way to translate them to SAP open SQL. Here is one of the closest ones.

Do you know how to solve it on the SAP AS from 7.52?