LASt 6 records of the output of the stored procedure

Question

The following is the stored procedure and "I want to fetch the LATEST SIX INVOICES FOR EACH CUSTOMER"

THERE COULD BE MORE INVOICES FOR EACH CUSTOMER BUT I HAVE TO FETCH ONLY WHICH ARE LATEST 6 INVOICES.

ALTER PROCEDURE [dbo].[SCA_M_CUSTSOINV_REFRESH]
  @COMP_CD NVARCHAR(20)='',
  @USER_CD NVARCHAR(20)='',
  @USER_TYPE NVARCHAR(1)=''
AS
SET NOCOUNT ON

DECLARE @SLSHIST_DATE NVARCHAR(10)

SELECT 
   @SLSHIST_DATE = CONVERT(NVARCHAR(10), DATEADD(MONTH,-SLSHIST_MTH,dbo.[GetCountryDate]()),120)
FROM SET_MASTER

WITH SUBQUERY AS 
(SELECT
   ROW_NUMBER() OVER (PARTITION BY A.CUST_CD ORDER BY C.INV_KEY DESC) "ROW_ID",
   A.CUST_CD,C.SO_KEY "TXN_KEY",
   C.INV_NO, C.INV_KEY, C.INV_DT, C.INV_STATUS, C.NET_TTL_TAX AS INV_AMT
 FROM 
    (SELECT DIST_CD, SLSMAN_CD, CUST_CD FROM T_CA_SLSMANCUST 
     WHERE DIST_CD = @COMP_CD AND SLSMAN_CD = @USER_CD) A
INNER JOIN TXN_INVOICE C ON C.CUST_CD=A.CUST_CD
                         AND C.INV_DT >= @SLSHIST_DATE
)
SELECT 
    CUST_CD, TXN_KEY, INV_NO, INV_KEY, INV_DT, INV_STATUS, INV_AMT,
    CASE ROW_ID WHEN 1 THEN 'Y' ELSE 'N' END "LAST_INV"
FROM SUBQUERY
ORDER BY CUST_CD,INV_KEY

Answer 1

SELECT TOP 6 * FROM invoices ORDER BY date DESC

Answer 2

You are getting ROW_ID by ROW_NUMBER() OVER (PARTITION BY A.CUST_CD ORDER BY C.INV_KEY DESC) and the last invoice is the one with ROW_ID = 1 so don't you just need to add WHERE ROW_ID <= 6 as below?

   SELECT 
        CUST_CD, TXN_KEY, INV_NO, INV_KEY, INV_DT, INV_STATUS, INV_AMT,
        CASE ROW_ID WHEN 1 THEN 'Y' ELSE 'N' END "LAST_INV"
    FROM SUBQUERY
    WHERE ROW_ID <= 6
    ORDER BY CUST_CD,INV_KEY