Hi,
I have data in a table as follows:

Key date

101 4/25/2006
101 4/26/2006
101 4/27/2006
101 4/28/2006
101 5/25/2006
101 5/26/2006
101 5/30/2006


I need the max of date for each month. So my output expected is:
101 4/28/2006
101 5/30/2006

How can I get this? Please help

FOR:-

101 4/25/2006
101 4/26/2006
101 4/27/2006
101 4/28/2006
101 5/25/2006
101 5/26/2006
101 5/30/2006

TRY:-

101 60425
101 60426
101 60427
101 60428
101 60525
101 60526
101 60530

This gives you the opportunity of SORTING.

Quote:

---

Originally Posted by ROLCAM

FOR:-

101 4/25/2006


TRY:-

101 60425


This gives you the opportunity of SORTING.

---

Transposing data is not the best answer. Do you actually know anything about SQL?

What you need to do is use a subquery. You need to create a query that adds a column to identify the month. You then need to use the TOP 1 qualifier and sort by date in descending order and group by month. This query is then used as a subquery in an IN clause to restrict your result set.