How to Select the last N records based on date?

[shapper]

Hello, I need to load the last N records from a table. The last N records are determined by date. And I want N to be a parameter in the Query. How can I do this? I allready have a Query. The only thing I am missing is that part. SELECT web_News.ArticleDate, web_NewsLocalized.ArticleTitle, web_NewsLocalized.ArticleText FROM web_News INNER JOIN web_NewsLocalized ON web_News.ArticleId=web_NewsLocalized.ArticleId WHERE ((web_NewsLocalized.ArticleCulture)=[@Culture]); Thanks, Miguel

[nguyenvhn]

If you are using SQL Server 2005, there is a simple way: SELECT TOP(@N) web_News.ArticleDate, web_NewsLocalized.ArticleTitle, web_NewsLocalized.ArticleText FROM web_News INNER JOIN web_NewsLocalized ON web_News.ArticleId=web_NewsLocalized.ArticleId WHERE ((web_NewsLocalized.ArticleCulture)=[@Culture]) ORDER BY web_News.ArticleDate DESC;

[shapper]

Hi, I am using Access. The top (@N) is not working. I get an error when trying to save the Query. I have been looking in internet but I can't find the solution. I also tried [@N] but no luck. Any idea? Thanks, Miguel

[nguyenvhn]

SELECT web_News.ArticleDate, web_NewsLocalized.ArticleTitle, web_NewsLocalized.ArticleText, (SELECT COUNT(*) FROM web_News AS B WHERE B.ArticleDate>=A.ArticleDate) AS RowNumber FROM web_News AS A INNER JOIN web_NewsLocalized ON web_News.ArticleId=web_NewsLocalized.ArticleId WHERE ((web_NewsLocalized.ArticleCulture)=[@Culture] AND RowNumber<=@N) ORDER BY web_News.ArticleDate DESC; I am not sure wheather it works, I've not tested yet, but just try it anyway. The performance will be a big problem