Top-N with a twist

From: Yosi Greenfield <yosi_at_comhill.com>
Date: Mon, 11 Sep 2000 17:13:17 -0400
Message-Id: <10616.116668@fatcity.com>

All,

Ok, so we know how to use nested views to return a top-n report, right? Use order by in the nested view and use rownum in the outside view. Great.

What about to doing this for groups? I can't figure it out and it's making me nuts. (I'm sure some would argue that opinion. ) Example - I want the top ten selling employees for each department, or I want the top ten diagnoses in each clinic.

I've even pregrouped and pre-summed the employees or pre-counted the diagnoses so its a simple non-aggregate query I'm running. I just want to limit the results to 10 or 20 or 30 rows per region or clinic or department.

(I think it's time to go back into biology and think about that pre-med degree I gave up. It's been that kind of day anyway.)

Any ideas? Pretty please!!?

Thanks,

Yosi

---

Yosi Greenfield
Database Architect
Comhill Systems, Inc. Received on Mon Sep 11 2000 - 16:13:17 CDT