Quick sql 2005 query question

[TheEarthWillShake]

This is a basic example of what I'm trying to achieve.

One table

Table TEMP
ID
Date

1 1/15/2007
2 1/25/2007
3 2/16/2007
4 2/25/2007

I want to return the most recent date within each month.

so I want

2 1/25/2007
4 2/25/2007

SELECT Date, ID
FROM temp AS ut
WHERE (Date =
(SELECT MAX(Date) AS Expr1
FROM temp AS ut2
WHERE (ID = ut.ID)))
ORDER BY Date DESC

This is what I have so far but I can't seem to figure out what to do next.

It returns everything with the latest date first.

3/25/2008 12:00:00 AM 6
3/15/2008 12:00:00 AM 5
2/25/2008 12:00:00 AM 4
2/15/2008 12:00:00 AM 3
1/25/2008 12:00:00 AM 2
1/15/2008 12:00:00 AM 1

Any ideas?

 

[Crusty]

You need to group on a MONTH(Date) value.

 

[KLin]

Originally posted by: Crusty
You need to group on a MONTH(Date) value.

Agreed. The OP will need to take it a step further by concatenating the year if he wants to get the most recent date in a month across different years.

Just select the max of the date and do an expression field for the month/year and group by that field.

 

[TheEarthWillShake]

I'm new to sql querys and I'm not quite sure what you mean :-x

(SELECT MAX(Date) AS Expr1 needs to be grouped?

 

[TheEarthWillShake]

I figured it out.

SELECT MAX(Date) AS Expr1
FROM temp
GROUP BY MONTH(Date), YEAR(Date)

 

[KLin]

Originally posted by: TheEarthWillShake
I figured it out.

SELECT MAX(Date) AS Expr1
FROM temp
GROUP BY MONTH(Date), YEAR(Date)

:thumbsup: for figuring it out. You didn't really need to concatenate it after all.