CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Richard Gale
Richard Gale

Reputation: 1952

Get Last six rows from a select query

I have written a stored procedure which gives me the raw data I require for Data Analysis.

This SP, at the minute populates a temporary table with the following structure:

CREATE TABLE #Analysis
(
    AgreementID int,
    DueDate datetime,
    DateFrom datetime,
    DateTo datetime,
    AmountDue decimal(9,2),
    AmountPaid decimal(9,2),
    DueTD decimal(9,2),
    PaidTD decimal (9,2),
    ArrearsScore int
)

AgreementID can repeat in this table between 6 records upwards.

What I need to do, is for each AgreementID, I need to Get the LAST 6 Rows

This will then allow me to carry out the rest of my analysis.

I have tried:

    SELECT
          AN.*
    FROM
          #Analysis AS A
    LEFT OUTER JOIN
         (SELECT 
             TOP(6) * 
          FROM 
              #Analysis 
          ORDER BY 
              AgreementID, DueDate DESC
         ) AS AN ON A.AgreementID = AN.AgreementID
    WHERE AN.AgreementID IS NOT NULL

But this does not give me the desired results.

Can anyone advise?

Thank you.

Upvotes: 1

Views: 157

Answers (1)

John Woo
John Woo

Reputation: 263733

If you are using SQL Server 2005+, you can use Common Table Expression and Windowing function. The ROW_NUMBER() gives a rank or value for every AgreementID which the record has value of 1 sorted from latest DueDate.

WITH records
AS
(
    SELECT  AgreementID,
            DueDate,
            DateFrom,
            DateToe,
            AmountDue,
            AmountPaid,
            DueTD,
            PaidTD,
            ArrearsScore,
            ROW_NUMBER() OVER(PARTITION BY AgreementID 
                        ORDER BY AgreementID, DueDate DESC) rn
    FROM    #Analysis
)
SELECT  AgreementID,
        DueDate,
        DateFrom,
        DateToe,
        AmountDue,
        AmountPaid,
        DueTD,
        PaidTD,
        ArrearsScore
FROM    records
WHERE   rn <= 6

without using CTE

SELECT  AgreementID,
        DueDate,
        DateFrom,
        DateToe,
        AmountDue,
        AmountPaid,
        DueTD,
        PaidTD,
        ArrearsScore
FROM    
        (
            SELECT  AgreementID,
                    DueDate,
                    DateFrom,
                    DateToe,
                    AmountDue,
                    AmountPaid,
                    DueTD,
                    PaidTD,
                    ArrearsScore,
                    ROW_NUMBER() OVER(PARTITION BY AgreementID 
                                ORDER BY AgreementID, DueDate DESC) rn
            FROM    #Analysis
        ) records
WHERE   rn <= 6

Upvotes: 2

Related Questions