Get top 2nd 3rd and 4th values in calculated field

[DesiLogi]

Hi,

I'm have a calculated field that gets values from a View by the earliest date. It puts together  a string (for html view) with 3 fields (date, project, and title) of the record so it can be displayed in an html block. It looks like this: 

CONVERT(VARCHAR(40),(SELECT TOP 1 DueDate
FROM _V_Client_Portal_Calendar_Notify Where PortalID='[@authfield:PortalID]'
ORDER BY DueDate ASC))
+ '<br />' +
(SELECT TOP 1 Project_Name
FROM _V_Client_Portal_Calendar_Notify Where PortalID='[@authfield:PortalID]'
ORDER BY DueDate ASC)
+ '<br /><i>' +
(SELECT TOP 1 Title
FROM _V_Client_Portal_Calendar_Notify Where PortalID='[@authfield:PortalID]'
ORDER BY DueDate ASC)

The issue is I need to then have another calculated field that gets the 2nd 'top' record, and another for the 3rd and 4th. How do you change this calculation to pull the 2nd record instead of the first?  The result is I what to be able to pull the next 4 events in a calendar table, one in each calculated field, so they can be displayed as notifications. 

[futurist]

Hi @DesiLogi,

I use this formula to get the other top records that arent the first:

 

SELECT Field_Date 
FROM (
    SELECT 
        ROW_NUMBER() OVER (PARTITION BY NULL ORDER BY Field_Date DESC) as RN,
        Field_Date 
    FROM Forum_Table_1 
) d
WHERE RN = 3

 

You'd simply have to change the 3 in WHERE RN = 3 with the nth record you want to get. So if you want to get the 2nd, just use 2, and so on.

[DesiLogi]

Hi @futurist,

Nice--works beautifully--thank you! 

I can use this to get the ID value of each specific record I need and then use that ID to pull all the field values for display--really excellent solution. 

[telly]

Hi Team, 

I just want to add a helpful article https://howto.caspio.com/function-reference/. I usually use it when creating a formula.