need query to provide distinct value based on timestamp for each id number
Posted on 2008-10-24
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!