Show only the top 10 records per Group

[leeland]

Hello,

Long time reader, first time poster :)

I am working on a report in crystal 8.5, and I have written an SQL statement that will return a random record set...

When I put that view into Crystal I want to group it by a provider...and show only a max of 12 records per that grouping.

(Some groupings will only have a couple of records in it)

What is the correct way to set up such a grouping that limits the record count to 10 to 12 ?



Any help would be appreciated...


I did look into the TOP N/SORT GROUP EXPERT...but wasn't getting the desired results...


Thanks in advance


Leeland

[leeland]

well don't everyone all jump at this one at once...


I figured it out...I had to use a formula and then clear the formula variable at the group footer level

[Ken55]

leeland, can you share the formula? I have the same need but not quite figure it out yet. Thanks!

[pawa]

Hi,
U can create a fomular to count records in each group. Then u supress details when count(records) > 10
Regards.

hientv78@yahoo.com

[Ken55]

Thanks pawa. I got it.

For Crystal newbie likes me.

1. setup a group count.
2. suppress detail if > 10 records.

[SOFTGURU]

Which you are doing it should be slower if you have huge data. I think it should be better if you create a storeprocedure like this..

CREATE PROCEDURE storeproc1
@RC int
As
SET ROWCOUNT @RC

Select .........

[Madhi]

That will select only top @RC of the first group :)

[pallavi.shinde]

Use Nth Largest

[Madhi]

Refer point 2 here
http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx

[Babs827]

How are you creating the count of records in each group?

I can create a summary count for the group but I don't think this is what I'm supposed to be doing?

Thanks,
Babs827

[bharathreddy511]

Finding TOP X records from each group

SQL> select * from emp e
2 where e.empno in (select d.empno from emp d
3 where d.deptno=e.deptno and rownum<3)
4 order by deptno
5 ;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7369 SMITH CLERK 7902 17-DEC-80 800 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

6 rows selected.

Thanks and regards
Bharath reddy

[ayazahmed78]

Step 1.
Create Running Total for Record (totRec) under each group. (Reset on group field)

Step 2.
In section expert suppress No Drill Down
totRec>10