SQL Query help

Posted on 2010-01-07
Last Modified: 2012-05-08
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:

row_number() over(partition by id order by id, historydate desc) as rownumber,
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.

Question by:catwalk
    1 Comment
    LVL 10

    Accepted Solution


    select *
    from (
          select *, ROW_NUMBER() over(partition by id order by id rownumber desc) rownum2
          from (
                row_number() over(partition by id order by id, historydate desc) as rownumber,
                from tblTable
          ) NumberedRecords
          where rownumber<=2
    ) T
    where rownum2=1

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    Suggested Solutions

    So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
    Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA.…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now