RankenIS
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.COUNSEL OR_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.
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER