February 15, 2008 at 8:37 am
Hi all, I am having an issue selecting only rows that contain the MAX(createdate). I will break down the table for simplicity.
Table: table1
Fields:
ID1 INT
ID2 CHAR(10)
CreateDate DateTime
There could be one or many rows for each ID2 that differ by CreateDate. I only want to return the row that contains the MAX(CreateDate). Here is what I have so far:
Select
a.ID1,a.ID2,a.CreateDate
From table1 a
Join table1 b ON
b.ID2 = a.ID2 AND
a.CreateDate = (Select max(b.CreateDate) From table1 b Where b.ID2 = a.ID2)
Where a.CreateDate >= '01/01/2008'
February 15, 2008 at 9:13 am
The following is ugly, but could do it:
SELECT a.ID1
, a.ID2
, a.CreateDate
FROM table1 a
WHERE a.CreateDate >= '01/01/2008'
AND a.CreateDate = ( SELECT MAX(b.CreateDate)
FROM table1 b
WHERE b.ID2 = a.ID2
)
Regards,
Andras
February 15, 2008 at 9:55 am
Hi,
When ever you do operations on the Date, Always use convert(varchar, GETDATE(), 101) function to take only date part. Coz in your query you have "=" operaor in this case you need to convert just to compare only date part.
Thanks -- Vj
February 15, 2008 at 9:59 am
Thank you for your contribution. The query you provided did the trick. Sometimes if you spend too much time thinking about a query, you end up overthinking and doing joins where thet are not necessary.
February 15, 2008 at 10:02 am
I agree on the post regarding using convert(char(10),mydate,101) for evaluating dates, however in this case I needed to include the time since some records have the same date portion.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply