I am busy with a stored procedure where I need to get the 4 last dates of different advertisements in a transaction table. (ms sql server)

This is the data in the transaction table:

id | AdvID | Date
1 | 14 | 13-2-2007 0:10:27
2 | 14 | 14-2-2007 0:10:27
3 | 14 | 15-2-2007 0:10:27
4 | 15 | 13-2-2007 0:10:27
5 | 15 | 15-2-2007 0:10:27
6 | 16 | 14-2-2007 0:10:27
7 | 16 | 16-2-2007 0:10:27
8 | 17 | 13-2-2007 0:10:27
9 | 17 | 15-2-2007 0:10:27
10 | 17 | 16-2-2007 0:10:27


These are the records that I need to have:

id | AdID | ValidThru
3 | 14 | 15-2-2007 0:10:27
5 | 15 | 15-2-2007 0:10:27
7 | 16 | 16-2-2007 0:10:27
10 | 17 | 16-2-2007 0:10:27

It will probably be very easy to get these records in a query but it's killing me for a while now

I hope someone can help me through this.

Thanks in advance!

Smiter

Now as far as i can see you are required to retrieve last 4 records up to a specific date.And if this is the case then all you have to do is to use the ORDER BY and WHERE clauses as needed:

EXAMPLE

SELECT * FROM
TRANSACTION
WHERE ValidThru >= 14-2-2007 0:10:27
ORDER BY ValidThru;

---------------------------
Try out this query, hope it helps and if you are still stuck then reply me back.


MAXOOD!

Life is an endless journey towards perfection

Try this query:

SELECT A.*
FROM [dbo].[YourTable] A INNER JOIN (SELECT [AdvID], MAX([ID]) AS [ID] FROM [dbo].[YourTable] GROUP BY [AdvID]) B ON A.[ID] = B.[ID]


SQL Server Helper
How well do you know SQL? Find out with the free test assessment from SQL Server Helper!!!
http://www.sql-server-helper.com/free-test/default.aspx

Got a SQL Server Question? Ask us here: http://www.sql-server-helper.com/forums/default.asp

Not sure if this is what you are looking for. Hope that helps.
Cheers.

_________________________
- Vijay G
Strive for Perfection