Link to home
Start Free TrialLog in
Avatar of Brad Bansner
Brad Bansner

asked on

need query to provide distinct value based on timestamp for each id number

This should be easy, its just a little more complicated than my query writing abilities. Three columns:

timestamp = datetime format for when the record was created
ccid = integer ID number (multiple records can have the same ccid)
postid = integer, either a 1 or a 2

I will write out what I want, hopefully someone can convert this to query format:

I want a list of DISTINCT ccid numbers WHERE the MOST RECENT timestamp FOR THAT ccid has a postid of 1.

So, only the most recent timestamp for each ccid counts, older records with the same ccid are discarded. So that gives a list of ccid's, then I just need a list of just the ones that have a postid of 1 (easy enough).

I realize this could be done in several steps, but I'm sure there is an efficient, slick way of doing it with a single query? Thanks!
ASKER CERTIFIED SOLUTION
Avatar of BrandonGalderisi
BrandonGalderisi
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Brad Bansner
Brad Bansner

ASKER

Thanks! I used part of that, work great.