Solved

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

Posted on 2008-10-24
2
185 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

749 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