CHALLENGE: Advanced Query / Summary

[PyroX]

Hey Folks.

Consider this a challenge, kind of.

Given this data in a sql table:

ID      %         Level
----    -----    -----
AB1     50       4
AB1     100      4
AB1     100      3
AB1     100      2
AB1     100      1
AB2     33       3
AB2     75       2
AB2     100      1
AB3     22       2
AB3     100      1
AB4     100      5
AB4     100      4
AB4     100      3
AB5     100      1

I want to query it in a manor which provides me with the highest value (column 3) where the percent (column 2) is 100. NOTE: there may be multiple items for a lvl, if they are not all 100% then that lvl is not complete. Results I seek in a summary format.

Example results using example data:

Level 5: 1
Level 4: 0
Level 3: 1
Level 2: 0
Level 1: 2

What are your thoughts on the best way to accomplish this? I would prefer a solution that is mostly sql with very little code, but would like to hear all your thoughts.

Thanks for looking!

[RedDragon]

hi this query should do what you want:

SELECT LEVEL , COUNT( id )
FROM table
WHERE percent = '100'
GROUP BY LEVEL LIMIT 0, 30

then fetch an array and echo it out:

for($x=0; $x<sizeof($array); $x++) {
echo "Level $array[$x][0]: $array[$x][1]<br />";
} 

[PyroX]

No, that is exactly what i do not want. That would return:

AB1 100 4

...even though another item for that id was only at 50%. Please re-read the question.

[RedDragon]

well i tested it locally and the output was:

Level 1: 4
Level 2: 1
Level 3: 2
Level 4: 2
Level 5: 1

can you make an example what would be the result wishes in your example!

[PyroX]

Yes, and there are only 5 item ID's so those counts are off.