SQL Query help

I have a table with ID's and transaction dates.  I want to get the second highest date and if there isn't a second highest date then i want the main record.

I have:

Select
row_number() over(partition by id order by id, historydate desc) as rownumber,
id,
date
from tblTable

Now, that i have the row number, i want all the records where it equals 2 and if an id does not have a 2, then i want the number 1 record.

catwalkAsked:
Who is Participating?
 
lofCommented:


select *
from (
      select *, ROW_NUMBER() over(partition by id order by id rownumber desc) rownum2
      from (
            Select
            row_number() over(partition by id order by id, historydate desc) as rownumber,
            id,
            date
            from tblTable
      ) NumberedRecords
      where rownumber<=2
) T
where rownum2=1
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.