Print Page | Close Window

How do I get the record with the most current date

Printed From: Crystal Reports Book
Category: Crystal Reports for Visual Studio 2005 and Newer
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=17974
Printed Date: 05 Apr 2025 at 6:35am


Topic: How do I get the record with the most current date
Posted By: crystalgal
Subject: How do I get the record with the most current date
Date Posted: 09 Nov 2012 at 1:10am
How do I get the record with the most current date for each person's ID?
I am doing this in sql.
right now I have
Select ID, testDate, Status1
from myTable a
where testDate = (select max(testDate) from myTable b
where a.ID = b.ID)

but this gives me the previous day date(when i ran today, it gave me yesterday's date)but in the databse the latest date is 11/1/2012
what am I missing?
TIA


Replies:
Posted By: lockwelle
Date Posted: 10 Nov 2012 at 4:13am
how about trying:
Select a.ID, x.testDate, a.Status1
from myTable a
 cross apply (
  select max(testDate) as testDate
  from myTable b
  where a.ID = b.ID
 ) as x


Print Page | Close Window