How to: Select Last Record for Each Record ID

Posted on 2006-06-01
Last Modified: 2010-08-05
My current query retrieves the following data

<Current Resultset>

id             LastContactDate

1              10-jan-06
1              11-jan-06
1              12-jan-06
2              10-feb-06
2              11-feb-06
2              12-feb-06

I need to be able to select only the latest date for each record id.

<Required Resultset>

id             LastDateContact

1              12-jan-06
2              12-feb-06

Thanks in advance
Question by:jfmckenzie
    LVL 75

    Accepted Solution

    You need to requery the database with something like this:

    Select id, MAX(LastContactDate)
    From YourTableName
    Group by id
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    Hi jfmckenzie,

    SELECT ID, MAX(LastContactDate)LastContactDate
    FRoM urTable

    Aneesh R!

    Author Comment

    Thanks guys. Both comments correct, not sure how to allocate points so just accepted first answer.
    LVL 75

    Expert Comment

    by:Anthony Perkins
    >>not sure how to allocate points <<
    See here from the EE Help:

    More than one Expert helped solve my problem. What do I do?

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Viewers will learn how the fundamental information of how to create a table.

    746 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

    16 Experts available now in Live!

    Get 1:1 Help Now