Db/2 – Query – Get Top N Rows for each group ( Function )

Background

Wanted to start querying our WideWorld database on Db/2 LUW.

Code

Outline

  1. Create Procedure
    • Parameters
      • Nth
    • ResultSet
      •  fiscalYear
      • SaleKey
      • profit
      • sequenceNbr
    • Not Deterministic
    • Ability to read SQL Data
  2. Common Table Expression ( CTE )
    • Windowing Function
      • Partition by Year

SQL


DROP FUNCTION func_queryMaxByYearCorrelatedJoin_GroupByActualColumn_WF
//

CREATE OR REPLACE FUNCTION func_queryMaxByYearCorrelatedJoin_GroupByActualColumn_WF
(
    @seqNbr int default 3
)
RETURNS TABLE
(

       fiscalYear       int
     , SaleKey          bigint
     , CustomerKey      integer
     , profit           decimal(18, 2)
     , sequenceNbr      int

)
LANGUAGE SQL

-- CARDINALITY 50

NOT DETERMINISTIC

READS SQL DATA 

RETURN

    WITH cteYearCustomer
    (
          InvoiceDateKeyYear
        , SaleKey
        , CustomerKey
        , Profit
        , sequenceNbr
    )
    as
    (
        select  

              tblFS.InvoiceDateKeyYear AS InvoiceDateKeyYear

            , tblFS.SaleKey AS SaleKey

            , tblFS.CustomerKey AS CustomerKey

            , tblFS.PROFIT AS PROFIT

            , ROW_NUMBER()
                OVER
                (
                    PARTITION BY
                          tblFS.InvoiceDateKeyYear

                    ORDER BY
                            tblFS.PROFIT DESC
                )                   

        from   "Fact"."Sale" tblFS

    )    

    SELECT

          tblFS.INVOICEDATEKEYYEAR

        , tblFS.SaleKey

        , tblFS.CustomerKey

        , tblFS.PROFIT

        , tblFS.sequenceNbr     

    from cteYearCustomer tblFS

    WHERE tblFS.sequenceNbr <= @seqNbr

//

Invoke


SELECT *

FROM   TABLE
            (
                func_queryMaxByYearCorrelatedJoin_GroupByActualColumn_WF
                (
                    3
                )
            ) tblP

Output

queryPartitionReturnNth.PNG

One thought on “Db/2 – Query – Get Top N Rows for each group ( Function )

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s