Find the second largest value with Groupings

user_12387628 0
Viewed 59

In SQL Server, I am attempting to pull the second latest NOTE_ENTRY_DT_TIME (items highlighted in screenshot). With the query written below it still pulls the latest date (I believe it's because of the grouping but the grouping is required to join later). What is the best method to achieve this?

SELECT 
    hop.ACCOUNT_ID,
    MAX(hop.NOTE_ENTRY_DT_TIME) AS latest_noteid
FROM
    NOTES hop
WHERE 
    hop.GEN_YN IS NULL
    AND hop.NOTE_ENTRY_DT_TIME < (SELECT MAX(hope.NOTE_ENTRY_DT_TIME)
                                  FROM NOTES hope
                                  WHERE hop.GEN_YN IS NULL)
GROUP BY
    hop.ACCOUNT_ID

Data sample in the table:

I would need the items highlighted

greatest-n-per-group tsql sql-server sql
2 Answers

One of the "easier" ways to get the Nth row in a group is to use a CTE and ROW_NUMBER:

WITH CTE AS(
    SELECT Account_ID,
           Note_Entry_Dt_Time,
           ROW_NUMBER() OVER (PARTITION BY AccountID ORDER BY Note_Entry_Dt_Time DESC) AS RN
    FROM dbo.YourTable)
SELECT Account_ID,
       Note_Entry_Dt_Time
FROM CTE
WHERE RN = 2;

Of course, if an ACCOUNT_ID only has 1 row, then it will not be returned in the result set.

The OP's statement "The row will not always be 2." from the comments conflicts with their statement "I am attempting to pull the second latest NOTE_ENTRY_DT_TIME" in the question. At a best guess, this means that the OP has rows with the same date, that could be the "latest" date. If so, then would simply need to replace ROW_NUMBER with DENSE_RANK. Their sampple data, however, doesn't suggest this is the case.

user_20299830

You can use window functions:

select *
from (
    select 
        n.*, 
        row_number() over(partition by account_id order by note_entry_dt_time desc) rn
    from notes n
) t
where rn = 2
user_106767160
Related