• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 194
  • Last Modified:

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!
0
bbdesign
Asked:
bbdesign
1 Solution
 
BrandonGalderisiCommented:
select y.* from YourTable y
join
(select ccid,max(timestamp) mts from YourTable group by ccid)m
on y.ccid=m.ccid
and y.timestamp=m.mts
where postid=1
0
 
bbdesignAuthor Commented:
Thanks! I used part of that, work great.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now