SQL Server view question (1 Viewer)

[ideefixe]

Hi all,

I am trying to generate a query in Access which will return the last five comments on a case. The Access is linked to a SQL Server backend. I thought I'd try building a view there. I want a view that looks like

Primary Key Comment ID
1 5
1 4
1 3
1 2
1 1

and so on. I know I can use the MAX function and then Max(CommentID) - 1 etc but the issue is, I want a separate row for each comment ID.

I cannot think of any way to do this that does not involve building a stored procedure or a function in T-SQL. But is it possible to include the resultset of a stored procedure in a SELECT statement in Access.

My concern is that I could end up wasting a lot of time creating the SP and then being unable to join it into the query in Access as a table view.

Edited to add: I also want to avoid any negative comment ID's having a row due to the computation.

I wonder does anyone have any thoughts on this?

Thanks very much

 

[FoFa]

Set up a query to return all results in the order you wish, then use SELECT TOP 5 and it will only return the top 5 rows for your cirtiera and sort.

 

[ideefixe]

My boss found the solution to the problem. It's a self-join:

DECLARE @NumberOfComments INT
SET @NumberOfComments = [value here]

Select PrimaryKey, CommentID, comment
From Comments_Table as A
Where (Select count(1) From Comments_Table Where rfino=A.RFINo and CommentID >= A.CommentID)<=@NumberOfComments