| Getting n max rows [message #374996] |
Tue, 17 July 2001 21:13  |
Angelos Tantalos
Messages: 2
Registered: May 2001
|
Junior Member |
|
|
I have the following query:
SELECT a.kod_ypal
,a.epon
,a.onom
,b.health_product_id
,b.name
,COUNT(b.health_product_id) COUNT
FROM V_YPAL_EIDIK a
,HIS_HEALTH_PRODUCTS b
,HIS_MED_PROC_PRESCRIPTS C
,HIS_MED_PROC_PRESCRIPTS_GRP d
WHERE a.kod_ypal = c.doc_doctor_id
AND c.med_proc_prescript_num = d.mpp_med_proc_prescript_num
AND d.hepr_health_product_id = b.health_product_id
AND c.type_flg = '1'
AND c.issue_date BETWEEN '1/1/1999' AND '1/1/2003'
AND a.kod_ypal BETWEEN '1' AND '99999'
GROUP BY a.kod_ypal,a.epon,a.onom,b.health_product_id,b.name
and i get the following result:
kod_ypal epon onom health_product_id name count
7320 Smith John 337 descr1 1
7320 Smith John 409 descr2 3
7320 Smith John 612 descr3 2
7321 Lou Nick 337 descr1 1
7321 Lou Nick 588 descr4 2
I want to select the n most 'occurent' rows,depending on count column, of each kod_ypal. Is there any way i can do it with SQL? e.g If i wanted to select the 2 most 'occurent' rows,my result set would be:
kod_ypal epon onom health_product_id name count
7320 Smith John 409 descr2 3
7320 Smith John 612 descr3 2
7321 Lou Nick 337 descr1 1
7321 Lou Nick 588 descr4 2
If i wanted the most 'occurent' row of each kod_ypal then i would want the following result set:
kod_ypal epon onom health_product_id name count
7320 Smith John 409 descr2 3
7321 Lou Nick 588 descr4 2
Thanx in adavance!
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
