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

[GregMalewski]

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?

[Sandra_Rossi]

There's CTE since ABAP 7.51. Example:

  WITH +counting AS (
      SELECT b~carrid, COUNT(*) AS total
      FROM sflight AS b
      GROUP BY b~carrid
      )
  SELECT *
      FROM +counting
      ORDER BY total DESCENDING
  INTO TABLE @DATA(itab)
  UP TO 3 ROWS.

[GregMalewski]

Thank you for the information about CTEs - I will look for a way to leverage it. The expected result for the data set from the post for the top 3 per category is 3 x <number of apps> = 6 records. The "UP TO 3 ROWS" in the last query will always limit the results to 3 which not the goal.

[Sandra_Rossi]

Correct. This thing has always been difficult in ABAP, and always needed Open SQL + additional ABAP lines to refine the result. In your case, the database needs first to count everything and it takes time, the network load to transfer the results to ABAP is probably much less intensive.

If you had ABAP 7.54, you could maybe use the window expressions, but in 7.52 it's still the old world.

Or do SQL directly on the database (what you don't want).

[ThorstenHoefer]

As suggestion to find a solution to get the top 3 entries per uname ,
you need maybe several steps without using the window rank expression.

The code could look like this:

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


select appid, max(total) 
from @lt_all_usage.
group by appid
into table @lt_app_max

do 3 times.
select  a~appid, a~uname, a~total 
from @lt_all_usage as a
where 
inner join @lt_app_max as b
  on b~appid = a~appid
 and b~total = a~total 
appending into table @lt_result


select a~appid, max(a~total) 
from @lt_all_usage as a
 inner join @lt_app_max as b
   on  a~appid = b~appid
   and a~total < b~total
group by a~appid
into table @lt_app_max
endo.

sort lt_result by appid.

r

[GregMalewski]

Thanks, Thorsten for the code. The problem that I want to get around is the output of the first query that returns for many systems landscapes over 100 000 000 records and it could be run frequently in at least three variants for different time periods: total, week and day.

[ThorstenHoefer]

Maybe you should consider to use hana sql function like this?

https://stackoverflow.com/questions/16720525/how-to-select-top-3-values-from-each-group-in-a-table-w...

 WITH cte AS

  ( SELECT name, value,
           ROW_NUMBER() OVER (PARTITION BY name
                              ORDER BY value DESC
                             )
             AS rn
    FROM t
  )
SELECT name, value, rn
FROM cte
WHERE rn <= 3 
ORDER BY name, rn ;

[Yasin]

Hi

Try to use the UP TO 3 ROWS you should get the top 3 records since your data is already sorted.

SELECT * from MARA into table ITAB UP TO 3 ROWS.

Regards

[ThorstenHoefer]

Hi,

please keep in mind, you need the top 3 for each group "uname".