Jump to content
  • 0

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


DesiLogi
 Share

Question

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. 

Link to comment
Share on other sites

3 answers to this question

Recommended Posts

  • 0

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.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...