I'm having trouble with a query of individuals and test scores.

There are five different test types, which are thankfully numbered so a descending list will get me most of the way home.

But I only need/want the top three for each individual. I know TOP 3 needs to be in the eventual query, but when I add this, I get only get a handful of results THE top scores/levels.

I've got something like this:

SELECT TOP 3 table1.TECH_ID, table1.CAPP_LVL, Comment
FROM
(SELECT ST_LVL.TECH_ID, ST_LVL.TEST_LVL, Max(ST_LVL.TEST_LVL_SCORE) AS MaxScore
FROM ST_TEST_LVL
GROUP BY ST_LVL.TECH_ID, ST_LVL.TEST_LVL
HAVING ST_LVL.TEST_LVL="0005" Or ST_LVL.TEST_LVL="0007" Or ST_LVL.TEST_LVL="0008" Or ST_LVL.TEST_LVL="0009" Or ST_LVL.TEST_LVL="0011"
GROUP BY TECH_ID, TEST_LVL
ORDER BY ST_LVL.TECH_ID, ST_LVL.TEST_LVL DESC) AS table1

INNER JOIN

ValTest ON table1.MaxScore = ValTest.PlaceLevel AND table1.TEST_LVL=ValTest.CappLvl
GROUP BY, table1.TECH_ID, table1.TEST_LVL, Comment
ORDER BY table1.TECH_ID

Check out this article

This previous thread has the answer you want.

Thanks. I did some forum search first, but this didn't come up in my results.

Appreciate the pointer.

You are welcome. Good luck with your project. (IMG:http://www.utteraccess.com/forum/style_emoticons/default/thumbup.gif)

Thanks for the help. I'm a moron. I can solve this simply by writing better queries.

Since the Tests are numbered and it's either or for two of them, I was able to do it by joining two subtables.

Not that you'd know from the details i gave... just thought I'd post this in case this type of solution would work for whoever might stumble across this thread...

SELECT Top3Lvl.TEST_ID, Top3Lvl.MaxLvl, LvlScore.MaxScore
FROM
(SELECT TestLvls.TEST_ID, TestLvls.MaxLvl
FROM
(SELECT TEST_ID, Max(TEST_LVL) AS MaxLvl
FROM ST_TEST_LVL
WHERE TEST_LVL="0008" Or ST_TEST_LVL.TEST_LVL="0011"
GROUP BY TEST_ID

UNION ALL

SELECT TEST_ID, Max(TEST_LVL) AS MaxLvl
FROM ST_TEST_LVL
WHERE TEST_LVL="0007" Or ST_TEST_LVL.TEST_LVL="0006"
GROUP BY TEST_ID

UNION ALL

SELECT TEST_ID, Max(TEST_LVL) AS MaxLvl
FROM ST_TEST_LVL
WHERE TEST_LVL ="0003"
GROUP BY TEST_ID) AS TestLvls
ORDER BY TestLvls.TEST_ID, TestLvls.MaxLvl) AS Top3Lvl

INNER JOIN

(SELECT TEST_ID, TEST_LVL, Max(TEST_LVL_SCORE) AS MaxScore
FROM ST_TEST_LVL
GROUP BY TEST_ID, TEST_LVL
HAVING TEST_LVL="0007" Or TEST_LVL="0008" Or TEST_LVL="0003" Or TEST_LVL="0006" Or TEST_LVL="0011"
ORDER BY TEST_ID, TEST_LVL) AS LvlScore
ON (Top3Lvl.MaxLvl=LvlScore.TEST_LVL) AND (Top3Lvl.TEST_ID=LvlScore.TEST_ID)
ORDER BY TEST_ID, Top3Lvl.MaxLvl

Thanks for posting your solution. (IMG:http://www.utteraccess.com/forum/style_emoticons/default/thanks.gif)