select latest records based on datetime

Posted on 2005-05-06
Medium Priority
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

Melih SARICA earned 400 total points
ID: 13947668
select username,productID,max(purchasetime) from log_table
 where purchasetime > '01/01/1998'
 group by username,productID

LVL 22

Author Comment

ID: 13947715
Melih, you are the man. Thanks a lot.

Jeffrey Zheng

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

839 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