First 5 of a each group

I have a field that has different entries. Is there a way I can pull the first 5 of each one?

I can get the count of each different one, I just don't see how to get the first 5 of each.

SELECT PostType, COUNT(*) AS NumberOfEntires
FROM ECNADetail GROUP BY PostType ORDER BY PostType

That returns

PostType

NumberOfEntires

1

4924

2

181

3

3621

4

695

How many distinct post types are there? You could do:

Select Top 5 * from ECNADetail Where PostType=1

Union

Select Top 5 * from ECNADetail Where PostType=2

Union

....

|||

Hi Tealc,

Try this script:

select * from ECNADetail as t
where (select count(*) from ECNADetail where PostType=t.PostType and entries>t.entries)<5 order by PostType,entries desc

|||

Your definition of first 5 entries of PostType is not clear.

The following solution is based a datetime column (if you have one)

For Sql Server 2005, you can try:

SELECT
*FROM(SELECT*, Row_Number()OVER(PartitionBy PostTypeOrderBy PostType, YourdatetimeColumnDESC)as seq
FROM
ECNADetail)t1
WHERE
 t1.seq<6
 
If you don't have the datetimecolumn, you can choose other column to decide the order to choose the top 5 for each group.

|||I ended up doing the union with each type selected as the top 5. This was a one time run thing.