Full Version: Creating a top 3 list for each individual in a database
UtterAccess Discussion Forums > Microsoft® Access > Access Queries
discosammy
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
ScottGem
Check out this article
balaji
This previous thread has the answer you want.
discosammy
Thanks. I did some forum search first, but this didn't come up in my results.
ppreciate the pointer.
balaji
You are welcome. Good luck with your project. thumbup.gif
discosammy
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
balaji
Thanks for posting your solution. thanks.gif
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.