Solved

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

Posted on 2008-10-24
2
181 Views
Last Modified: 2013-11-05
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
Comment
Question by:bbdesign
2 Comments
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
ID: 22797929
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
 

Author Comment

by:bbdesign
ID: 22798743
Thanks! I used part of that, work great.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

743 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

13 Experts available now in Live!

Get 1:1 Help Now