select latest records based on datetime

Posted on 2005-05-06
Last Modified: 2010-03-19
I have a log table like this:

username productID purchasetime
user1          12           01/02/1999
user1          12           03/05/2000
user2          23           02/03/2000
user2          31           02/04/2000
user2          23           02/04/2000

I want to select users that purchased product later than a predefined time, say 01/01/1998, but if a user purchase a same product at different dates, then only the latest one will be selected. How to write the query? Thanks a lot.
Question by:prairiedog
    LVL 19

    Accepted Solution

    select username,productID,max(purchasetime) from log_table
     where purchasetime > '01/01/1998'
     group by username,productID

    Melih SARICA
    LVL 22

    Author Comment

    Melih, you are the man. Thanks a lot.

    Jeffrey Zheng

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    729 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

    21 Experts available now in Live!

    Get 1:1 Help Now