How to select bottom N rows from each group - Oracle 11g

Question

I have a table as follows:

+-----------------+----------+-------------+-------------+
| QCD_OUTLET_CODE | QCD_YEAR | QCD_QUARTER | QCD_CREDITS |
+-----------------+----------+-------------+-------------+
|       144034911 |     2015 | Q2          | 269.5       |
|        10500106 |     2015 | Q2          | 303.35      |
|       144034911 |     2015 | Q1          | 231.85      |
|        10500106 |     2015 | Q1          | 250.4       |
|        10500106 |     2014 | Q4          | 276.5       |
|       144034911 |     2014 | Q4          | 224.5       |
+-----------------+----------+-------------+-------------+

I need to display bottom 2 rows ordered by QCD_YEAR and QCD_QUARTER such that the latest quarter has the last rank (displayed as last row for each group) Previously, when there was only 2014/Q4 and 2015/Q1 (i.e., two records for per QCD_OUTLET_CODE), the following query worked fine to display the records in the order I wish:

WITH ordered 
     AS (SELECT qcd_outlet_code, 
                qcd_year, 
                qcd_quarter, 
                qcd_credit, 
                Row_number() 
                  over ( 
                    PARTITION BY qcd_outlet_code 
                    ORDER BY qcd_outlet_code, qcd_year, qcd_quarter) 
                AS rn 
         FROM   QTR_CREDIT_DATA) 
SELECT d.qcd_outlet_code    AS "Outlet_Code:string", 
       d.qcd_quarter 
       ||' ' 
       ||d.qcd_year         AS "MCT_quarter:string", 
       Nvl(d.qcd_credit, 0) AS "MCT_Total_Credits_Earned", 
       d.rn                 AS "Display_Order:string" 
FROM   ordered d 
WHERE  rn <= 2; 

The result for two rows for per QCD_OUTLET_CODE:

+--------------------+--------------------+--------------------------+----------------------+
| Outlet_Code:string | MCT_quarter:string | MCT_Total_Crecits_Earned | Display_Order:string |
+--------------------+--------------------+--------------------------+----------------------+
|           10500106 | Q4 2014            | 387                      |                    1 |
|           10500106 | Q1 2015            | 482.75                   |                    2 |
|          144034911 | Q4 2014            | 269.5                    |                    1 |
|          144034911 | Q1 2015            | 276.5                    |                    2 |
+--------------------+--------------------+--------------------------+----------------------+

Please ignore the numbers of QCD_QUARTER, they are arbitrary.

So Display_Order:string will display 1 and 2. However, with additional row for each QCD_OUTLET_CODE I need also to show the bottom 2 rows per group (i.e., 2015/Q1 and 2015/Q2) and I need them the have the values 1 and 2 for Display_Order:string

Running the same script above will return the same result above (2014/Q4 and 2015/Q1). If I use DESC in Partition clause, it will return the rows I want, but Display_Order:string will not have a correct value (`2015/Q2' will be give 1 instead of 2).

The result I wish from the first data set I posted above as follows:

+--------------------+--------------------+--------------------------+----------------------+
| Outlet_Code:string | MCT_quarter:string | MCT_Total_Crecits_Earned | Display_Order:string |
+--------------------+--------------------+--------------------------+----------------------+
|           10500106 | Q1 2015            | 387                      |                    1 |
|           10500106 | Q2 2015            | 482.75                   |                    2 |
|          144034911 | Q1 2015            | 269.5                    |                    1 |
|          144034911 | Q2 2015            | 276.5                    |                    2 |
+--------------------+--------------------+--------------------------+----------------------+

For its worth, the logic I'm trying to implement is like a window that slides over each group to display the latest two quarters, and give rows numbers accordingly.

Answer 1

Try this:

WITH ordered 
     AS (SELECT qcd_outlet_code, 
                qcd_year, 
                qcd_quarter, 
                qcd_credit, 
                Row_number() 
                  over ( 
                    PARTITION BY qcd_outlet_code 
                    ORDER BY qcd_outlet_code, qcd_year DESC, qcd_quarter DESC) 
                AS rn 
         FROM   QTR_CREDIT_DATA) 
SELECT d.qcd_outlet_code    AS "Outlet_Code:string", 
       d.qcd_quarter 
       ||' ' 
       ||d.qcd_year         AS "MCT_quarter:string", 
       Nvl(d.qcd_credit, 0) AS "MCT_Total_Credits_Earned", 
       Row_number() 
                  over ( 
                    PARTITION BY qcd_outlet_code 
                    ORDER BY qcd_outlet_code, qcd_year, qcd_quarter)
                           AS "Display_Order:string" 
FROM   ordered d 
WHERE  rn <= 2;