SQL question

[rmelnyck]

Here is my question:

I have s eimple table called giving_by_date which contains 3 columns ID, Amount and Date.

I need a SQL statement to get the 3 latest amounts for each ID number in the following table.

ID Amount Date
1 10 20051001
1 15 20050223
1 12 20050521
1 25 20050221
2 5 20050413
2 10 20050130
2 100 20050606
2 50 20050115
2 35 20050524

So what I want is a select that gives ID, Amount and Date but only the latest 3 in terms of date for each ID.

The result set should be

1 15 20050223
1 12 20050521
1 25 20050221
2 5 20050413
2 10 20050130
2 50 20050115

Any ideas?

 

[DotNetGnat]

SELECT A.ID, A.Amount, A.Date
From mytable A WHERE
A.Date IN ( SELECT Top 3 Date
FROM mytable B WHERE A.ID=B.ID
Order by Date Desc)

-DNG

 

[rmelnyck]

Awesome thanks.

 

[DotNetGnat]

no problem..glad to be of help...

-DNG