Link to home
Start Free TrialLog in
Avatar of RankenIS
RankenISFlag for United States of America

asked on

Need to return only x # of rows per distinct entries in another table

I need to get a result set showing COUNSELOR_RESPONSI.COUNSELOR_TITLE and the top three COUNT(CANDIDATE.CUR_PROG) per each COUNSELOR_TITLE.  I've been able to build the total result set, just not the top 3 by COUNSELOR_TITLE.

Desired result would be like:
Name          Program     Count
counselor1    cur_prog1   3
counselor1    cur_prog2       2
counselor1    cur_prog3       1
counselor2    cur_prog1   10
counselor2    cur_prog2   8
counselor2    cur_prog3   4


Hopefully that makes sense to someone.
SELECT     COUNT(CANDIDATE.CUR_PROG) AS Count, CANDIDATE.CUR_PROG, COUNSELOR_RESPONSI.COUNSELOR_TITLE
FROM         CANDIDATE INNER JOIN
                      NAME_MASTER ON CANDIDATE.HIGH_SCHOOL = NAME_MASTER.ID_NUM INNER JOIN
                      AD_ORGANIZ_MST_EXT ON NAME_MASTER.ID_NUM = AD_ORGANIZ_MST_EXT.ID_NUM INNER JOIN
                      COUNSELOR_RESPONSI ON AD_ORGANIZ_MST_EXT.COUNSELOR_RESP_INI = COUNSELOR_RESPONSI.COUNSELOR_INITIALS
WHERE     (CANDIDATE.CUR_YR = '2008') AND (CANDIDATE.CUR_STAGE = '32')
GROUP BY CANDIDATE.CUR_PROG, CANDIDATE.CUR_YR, COUNSELOR_RESPONSI.COUNSELOR_TITLE
ORDER BY COUNSELOR_RESPONSI.COUNSELOR_TITLE, Count DESC

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Raja Jegan R
Raja Jegan R
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of RankenIS

ASKER

Thank you for your quick, accurate response!