"Find top 3 order records in terms of amount in each month the current year: SQL WITH m AS( SELEC .."

mars RaqForum 25 No.
235 View • 1 Years ago

2.5 Getting records containing top/bottom N

Find top 3 order records in terms of amount in each month the current year:

SQL

WITH m AS(
    SELECT *,rank() OVER(PARTITION BY year(OrderDate),month(OrderDate) 
        ORDER BY Amount DESC) r 
    FROM Orders 
    WHERE OrderDate>='2022-01-01')
SELECT * 
FROM m 
WHERE r<=3

SPL

	A
1	>st=date(“2022-01-01”), start=days@o(st)
2	=file(“Orders.ctx”).open().cursor@x(OrderDate, Amount; OrderDate>=start)
3	=A2.groups(month@y(OrderDate):months; top(3;-Amount)).conj(#2)
4	=A3.run(OrderDate=date@o(OrderDate))