Wednesday, August 4, 2010

Select Top N Rows Per Group

I had an interesting sql task today. Someone asked me to get a max of three time-sheet entries per project. When an employee fills out a time-sheet they select a project for which the time is applied to. I was asked to grab a max of three of these entries and dump them in a csv for the accountant to audit. I was troubled on where to start this task. I am not an sql guy, so this was a challenge for me. I talked to the sql guys, but they were pretty busy today. One helped me but are minds together struggled to find the answer. After the googling, I tried a search result called "Cosmo Central" and found the anwser. Below is the piece that helped me so much...


with data as(
select row_number() over(partition by projects.name order by timesheet.id) as 'RowNumber’,
timesheet.id, projects.name
from timesheet
inner join projects on projects.id = timesheet.project_id
)

select id, name
from data
where RowNumber <= 3


Resource Used:
http://www.cosmocentral.com/2010/04/select-top-n-rows-per-group-ms-sql/

Yes. It looks like I copied the post and re-posted, but I am more so happy that I found the answer that I wanted to share the solution again. The credit still goes to Cosmo Central. Thanks a bundle.

No comments:

Post a Comment