We help IT Professionals succeed at work.

SQL Server 2008 : Tricky Top 5 Rows Problem

I have a tricky problem I am trying to solve with either Multiple Views or perhaps a Procedure to create a temp table that is returned...
.
There is a Report that Shows the Last 5 comment entries for every Project
.
There is a table :  tblFolderComments
This table has fields :
  -  FolderCommentID (Key)
  -  ProjectID
  -  FolderComment
.
.
These Projects can have any number of comments from 0 to 20+

I need to create something where I only get the most recent 5 [FolderComments] from each Project
.
I thought this was going to be simple ... but it has turned out not to be
.
Comment
Watch Question

Not sure exactly what you are asking for, but assuming that FolderCommentID is an identity column of some kind:

select top 5 *
from tblFolderComments
where ProjectID = 1234
order by FolderCommentID desc
nevermind, I see what you need now is the same thing but for all ProjectIDs at once, correct?

Author

Commented:
Not quite .. I wish it was that easy
.
you are including a ProjectID filter , where I need ALL ProjectIDs
.
All ProjectIDs , not a single one
.
Commented:

SELECT 
   *
FROM 
   ( SELECT  
       FolderCommentID 
       ,ProjectID
        ,FolderComment
        ,ROW_NUMBER() OVER (PARTITION BY ProjectID ORDER BY FolderCommentID DESC) RN
    FROM 
        YourTable ) a
WHERE
   a.RN <= 5

Open in new window

Author

Commented:
Dude .. you are Awesome !

Author

Commented:
Tim_cs  :  Awesome quick SQL code